Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: Access Junction Table Design

    I have three populated tables in Access…Patients, Medications and PatientMeds as a Junction table. No relationships yet.

    Patients table = Patient_ID(PK no dups), FirstName, LastName etc.
    PatientMeds table = Patient_ID(non unique), Med_ID(non unique), Dose, Unit, Freq etc.
    Medications table = Med_ID (PK no dups), MedName, MedManuf etc.

    I queried the data into the PatientMeds junction table with the Patient_ID and Med_ID fields as number and not defined as primaries.
    On trying to apply a composite key(dups OK) in the PatientMeds table to Patient_ID and Med_ID, I received the “ The changes you requested were not successful because they will create duplicate values….error), which makes sense as those fields do contain duplicate values, but with dups OK I thought it should work. An example of how the data appears:

    PatientMeds table

    Patient_ID Med_ID Dose
    001 815 5
    002 612 10
    003 112 20
    002 055 10
    001 552 10

    The example schema I’ve been working from shows the junction fields as primary keys, I then checked the Access Inside Out book and that does not show the fields as primary keys.
    Do I have a table design error? Do the two junction fields need to be primary? I’m fairly new to Access, maybe I’m missing a concept.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    By composite key, do you mean composite index or did you try to create a primary key including both Patient_ID and Med_ID?
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    I created a primary key including both Patient_ID and Med_ID. Highlighted both fields and chose Primary Key. I also tried creating a primary key on each field individually with the same result.
    Last edited by RxExec; 07-25-11 at 20:58.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by RxExec View Post
    I created a primary key including both Patient_ID and Med_ID. Highlighted both fields and chose Primary Key.
    You can't have duplicate values in a primary key.
    Quote Originally Posted by RxExec View Post
    I also tried creating a primary key on each field individually with the same result.
    You can't have more than one primary key for a table.
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    14
    So the Med_ID and Patient_ID of the junction table would be foreign keys to the other two tables to create the many to many relationship, Yes?

  6. #6
    Join Date
    Jul 2011
    Posts
    14
    Just in case someone else is looking. The composite key links two fields together to form a unique identifier, in my case, patient_id and med_id. A duplicate query of those two fields combined showed that I did have duplicates. an earlier search on each individual field found nothing. Fixed those and I was able to apply the composite primary key with no errors. I applied relationships and it works perfectly.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Glad you were able to solve the problem.
    Have a nice day!

Posting Permissions

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