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:
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.
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.