Results 1 to 2 of 2
  1. #1
    New Lounger
    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.

  2. #2
    Plutonium Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts