I am building a database in Access 2003 to track medications on my patients. I am using the Northwind sample as model. Customers are now patients with MedRecNo's. Orders are now Contacts. The Medications would be like order details, i.e. the medications "on order" at the time of the visit.
At the time of a new contact, I want to update the the medication list using the list from the prior contact for that patient. For example, if the patient with MedRecNo 1234 comes in for a visit I need a query which will take the medlist from the last contactNo for that MedRecNo and import the medication list to the new contact number.
tblMedications tblContacts tblPatients
* ContactNo <-------- *ContactNo
*MedicationID MedRecNo <-------- *MedRecNo
Name, etc MedRecNo PatientName, etc.