Problem relating a junction table

G

Guest

I inherited a medical records type Access 2002 database that was in flat file
format. In trying to rectify this, I generated a joining table (#2) to spin
off each patient’s multiple diagnosis codes into a separate record per code
with the unique patient ID# and the order of the particular diagnosis code,
e.g., first, second and so on. I want to be able to relate a third reference
table that contains all possible diagnosis codes and text description for
each code, each its own record (some 21K records).

I tried to establish the relationships between the three tables: “Patientsâ€
(primary key= patient ID#), and “Diagnosis reference†(primary key=diagnosis
code) to the junction table with those two fields as its foreign keys.
However, I kept getting the error message that I couldn’t make those changes
because they would create duplicate values in the index, primary key or
relationship, and the suggestion to either change the duplicate data, remove
the index and/or remove or redefine the index to permit dups.

To recap-the Patients table contains records with unique patient ID#s, the
Diagnosis reference contains records with unique values for diagnosis codes.
Each of these has a one-to-many relationship with the junction table that
contains multiple records per patient ID# with a different order # and
diagnosis code.

Any guidance will be most greatly appreciated. Thank you.
 
G

Guest

A thought comes to mind - did you create the junction table with an index?

Did you build the three tables from scratch or used the existing table and
tried editing?

I suggest building from the get-go, setting relations and then appending the
data. Because the flat file has the patient multiple times you will get an
error on appending - say ok - then run a query to see if all were captured.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top