How to add a record ID to a access table

[lusvyat]

I have a table in access that has only two fields: one is the PATIENT_ID and the second is DRUG_NAME. There are multiple records with different DRUG NAMES for each patient ID. I am trying to figure out a way to add a third field that would say DRUG_1, DRUG_2, etc for each patient ID. So, the DRUG_1 would start new when it's a new PATIENT_ID

Any thoughts?

[George1111]

I think you have 2 tables from what you are saying

Table 1 - Patient_File

Patient_ID <<<<UNIQUE KEY
Patient_Name
Patient_DOB
Patient_Address
Patient_Phone
etc etc etc etc


Table 2 - Patient_Drugs_File

Patient_ID <<<<<NON-UNIQUE KEY - Same as Patient_ID in Table 1
Drug_Name



Table 2 can hold any number of records belonging to the Patient_ID in Table 1


Table 2 could also hold more information about the drug if required

Table 2 - Patient_Drugs_File

Patient_ID <<<<<NON-UNIQUE KEY
Drug_Name

Drug_Group
Date_Prescribed
Instructions


For example Drug_Group could even identify a problem when a Doctor prescribes a Drug to a Patient who has an Allergy to a certain group of drugs (held in the "Patient_Drug_Allergies" Table