W
Wannabe-Access-savvy
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.
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.