Junction tables

[Anne]

I used Access long ago, and am now using Microsoft Access 2003. All data is
currently in multiple Excel files. I am migrating them to Access 2003.

I am starting with three tables in Access: Hospitals, Clinics, and Contacts.
The Hospitals and Clinics tables contain address and phone numbers for each
institution.
The Contacts table contains a list of physicians, nurses, and other
personnel related to the hospitals and clinics.
Each contact may serve more than one clinic and/or hospital.

The goal is to be able to enter a single hospital or clinic name (i.e.
General Hospital) and retrieve all names from the contact table related to
this hospital.
The same is true for the reverse: to be able to enter a single contact name
(i.e. Dr. Smith) and retrieve all of the hospitals and clinics that Dr. Smith
is listed as a contact for.

My question deals with how to build the relationships between the tables.
Do I need to combine the Hospital and Clinics table, and create a junction
table that will use the primary keys from this and the Contact table?

Can I keep the three tables separate, and somehow create relationships that
will result in successful queries, without having to repeat information in a
junction table?

Can a junction table be used with more than two tables?

Let me know if I need to include more information.

Thanks.

 

[KARL DEWEY]

Do I need to combine the Hospital and Clinics table,
Combine and add field to designate Hospital/Clinic. Also add an Autonumber
as primary key.
I do not see a requirement for junction table. Add a number field, long
integer, as a foreign key and set a one-to-many relationship from
Hospital_Clinic table primary key. Use a form/subform with Master/Child link
using the Hospital_Clinic table primary key and Contact foreign key.

 

[Pindaro]

Hi Anne,

As you suggest yourself the appropriate way to go is to create a
many-to-many relationship between hospitals and contacts. Also another one
between clinics and contacts. To do this you will need to create a junction
table whose primary key will be the combination of the primary keys of the
tables that you would like to join.

So if the junction table for Hospitals and Contacts is named
tbl_HospitalsContacts, then its primary key will be (HospitalID, ContactID).
This is a composite primary key based on two fields.

Then, you are free to work with your data both ways.

 

[Pindaro]

Hi Anne,

You also mention that one contact person might serve more than one
hospitals/clinics. If this is the case, you need to be able to see the
number of hospitals by contact name. If this is correct, then you needs to
create a many-to-many relationship and thus a junction table so that you can
work both ways.

Is this what you would like to do?

 

[Larry Daugherty]

FWIW you might want to list both Hospitals and Clinics in a common
table: tblOrganization. The type of organization becomes an attribute
in the new table. That will significantly reduce the number of
relationships and forms and reports that you need to manage. You
might then have a single junction table: tblOrganizationContacts that
handles the many-to-many relationship. That will provide the hooks
for all of the rest of the things you want to do.

HTH