It would help knowing what are the real things being modeled here. In most cases, instead of numbered fields such as diag01, diag02, diag03... one should use individual records in a related table.
I know what you are saying. The primary table that has diag01...diag24 is part of one patient record. The records are imported from a csv file from another program. I have contacted the company that provides the data to include a field for the diag description in the csv. They are working on that. However, for reporting puposes we need the description now, so I created the table Diag_Desc with the three fields I indicated thinking I could create the relationship based on matching diags and by having each record in the primary table have D in the type field.
The way you have it now, you would need to set up 24 relationships between the Patients table and the Diag Desc table, to get the correct description for each diagnosis.
I have contacted the company that provides the data to include a field for the diag description in the csv.
There's no need for them to do so - but they probably could make the format easier for you to import (you need to import the patient - if new - into the Patients table, and their diagnoses into a Diagnoses table). Failing that, you could still import the data into a temp table (similar to your existing table) and then break it up into the 'real' tables.
I didn't quite get the part with the "D" in the type field. If all diagnoses are of type "D", then this field is redundant in both tables.
You are correct - I do not need the D in the patient record. I am still confused about what you are saying in regards to breaking down the patient record. The patient record is one record in the Patient Table, which is my primary table in the database, which happens to have 24 diagnosis fields in it.
The Diag Description table has three fields Type, Diag Code and Description.
Here is how the relationship is set:
Table: Patient Table: Diag Description
Field: diag01 = Field: Diag Code
Result on my form: diag01: V89 Description (from Diag Description table): Admin Proc
Problem is when you add diag02 so you have the following
Field: diag01 = Field: Diag Code
Field: diag02 = Field: Diag Code
The result is a blank description for both diag01 and diag02. It is basically a one to one relationship for each field. diag01 or 02 is looking for a match from the Diag Code field. Thanks.
It is basically a one to one relationship for each field.
That's true - and that's why you need a separate relationship for each field. Adding a matchfield to the existing relationship is not going to work, because there is no record in the Diag Description table that meets the condition:
Patients::diag01 = Diag Description::Diag Code
Patients::diag02 = Diag Description::Diag Code
unless diag01 and diag02 happen to contain the same code.
I am still confused about what you are saying in regards to breaking down the patient record. The patient record is one record in the Patient Table, which is my primary table in the database, which happens to have 24 diagnosis fields in it.
I am suggesting a structure of three tables: Patients, Diagnoses and DiagnoseDescriptions, related as follows:
Patients::PatientID = Diagnoses::PatientID
Diagnoses::Diag Code = DiagnoseDescriptions::Diag Code
Instead of 24 fields in the patients table, you will have 24 (or any other number) of related records in the Diagnoses table - and each one of these will fetch its own matching description from the DiagnoseDescriptions table.
So are you saying create 24 separate tables to import the diagnosis into? Otherwise, in the diagnosis table I will have patient id and 24 diag fields which is what I have now.
So are you saying create 24 separate tables to import the diagnosis into?
Certainly not. You only need one Diagnoses table. In this table, you will have ONE diag field and 24 RECORDS per patient.
Thanks - I will give it a try today.
Well, I have had success. In the relationship window I created 24 copies of the Diag Table and linked each copy to a different diag in the patient table. The Diag Table fields are Diag Code and Description.
Patient Table Diag Table 01 Diag Table 02 Diag Table 03
and so on to Diag24
The description is then linke to Diag01, Diag02....Diag24.
Thank you for your help on this all the comments helped me get this done. Hal
If you read Comment's posts correctly you'll see he suggests ONE Diagnoses table. There's no need for all the copies.
As I mentioned earlier, that is the only way to do it with your current structure. However, it is not a good solution, and you are likely to encounter limitations. A structure of three tables is both simpler and more flexible.