Results 1 to 2 of 2
2007-10-24, 20:06 #1
- Join Date
- Jul 2006
- Thanked 0 Times in 0 Posts
Multiple Records Coordinating with One Field (Access 2003)
I am wondering how I can create multiple records for one table entry and have my queries read all the information that could be linked.
For example, if I have a doctor named SALLY WRIGHT and she has 6 different provider numbers associated with her name, how can I avoid having her name in my table 6 times with each different provider numbers? I have to put percentages to each doctor and end up typing them 6 times over, when I should be only having to type it once, on the main table. I want one table for all doctors, then something that links their individual numbers behind the scenes when I enter them.
I hope that makes sense.
2007-10-24, 21:22 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: Multiple Records Coordinating with One Field (Access 2003)
You need to set up three tables:
- A table for the doctors, with (preferably) an AutoNumber field DoctorID as primary key, and information that is unique to each doctor, such as first name, last name, etc.
- A table with provider information, with an AutoNumber field ProviderID as primary key, plus information such as provider name etc.
- An intermediary table with number fields DoctorID and ProviderID (their combination is the primary key) plus information pertaining to the doctor-provider combination, of necessary.
The intermediary table is linked to the doctors table on DoctorID and to the providers table on ProviderID.
This is called a many-to-many relationship. See <post:=364,203>post 364,203</post:> for a sample database how to handle a many-to-many relationship with forms and subforms.