Help withTable Design and Relationships

[SteveD]

My database contains three tables. 1. A table of state contacts, 2.
regional contacts, and 3. Application information.
tblApplicationInformation includes the following fields
[ID],[AppType],[ProvType], and [FacState]. I need the database to be
able to automatically figure out who the state contact is and who the
regional contact is based upon [AppType],[ProvType], and [FacState].
These 3 criteria will select a single contact from tblStateContacts
and a single contact from tblRegionalContacts. What is the best way
of doing this?

 

[Anne Troy]

Hi, Steve. I would probably first put my state and regional contacts into
one table. Then add a column to denote which type of contact they are...that
is, unless the information you track on these guys is a lot different
depending on which type they are.

If "these 3 criteria" will select a single contact, then it sounds to me
like you have a one-to-one relationship? What's the point of the separate
table then?

I hope I'm wrong and some other expert will help you better.
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Com.Piersontech@Dreamboat
(Reverse it!)
Web: www.TheOfficeExperts.com
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->

 

[SteveD]

Actually, I could put the contacts into the same table. The reason I
didn't is because the State Contacts have a relationship with a table
of State Office addresses. The same goes for the Regional Contacts.

My problem is determining the contacts based upon
[AppType],[ProvType], and [FacState]. Ex. If an application comes in
with the following information: AppType = Initial, ProvType =
Hospital, and FacState = AL, then I need to locate the correct State
(and Regional) Contact that satisfy these criteria. Also, a contact
may cover 5 different provider types and 4 different states. How can
I show this in my tables? I only want a contact listed once. Please
help.