If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Junction tables
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. -- Anne |
#2
|
|||
|
|||
Junction tables
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. and create a junction table that will use the primary keys from this and the Contact table? 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. -- KARL DEWEY Build a little - Test a little "Anne" wrote: 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. -- Anne |
#3
|
|||
|
|||
Junction tables
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. "Anne" wrote: 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. -- Anne |
#4
|
|||
|
|||
Junction tables
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? "Anne" wrote: 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. -- Anne |
#5
|
|||
|
|||
Junction tables
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 -- -Larry- -- "Anne" wrote in message ... 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. -- Anne |
Thread Tools | |
Display Modes | |
|
|