asked on

Access databse query and table update/append

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.

rreiss60

ASKER

Revision: 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 (let's say contactNo 67) for that MedRecNo and import the medication list to the new contact number (contactNo 253). That is, the contactNo's aren't consecutive.

als315

If you have form for new visit (contact), you can select patient there and add new record to table tblContacts. Then, if ContactNo is autonumber, you can create query for selecting max ContactNo for this patient (it is presented on your form). This ContactNo should be less just added ContactNo, then with update query add all records from tblMedications for this ContactNo, but ContactNo should be new (added).
May be you can upload example from your DB (tables and forms) with removed sensitive data?

rreiss60

ASKER

Here are the tables and forms. Patients1.mdb

rreiss60

ASKER

Thank you for the suggestion of the update query. I can build a selection query that returns the medications based on a patient's most recent contactNo but I need help with the construction of the update query.
This is the select query:
SELECT tblMeds.ItemIndex, tblMeds.Name, tblMeds.ContactNo
FROM (tblPatients INNER JOIN tblVisits ON tblPatients.MedicalRecordNumber = tblVisits.MedicalRecordNumber) INNER JOIN tblMeds ON tblVisits.ContactNo = tblMeds.ContactNo
WHERE (((tblMeds.ContactNo)=[Forms]![fmMeds]![ContactNo]));

I then change the query type to "Update" but I don't know how to fill in the "update to" row.

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rreiss60

ASKER

Thank you. Unfortunately I rec'd an error message or two trying to run the form as you instructed. I will look at the queries further so I can see what you were trying to do. If I can't figure it out, I will repost the question. Hopefully I'll be further along and will just need minor help.

rreiss60

ASKER

Good solid try. Access file wouldn't run without error messages.

als315

Try this. I have different language version of Access and, when some autonamed objects contains russian letters, you can have problems with it. I've found one such name.
Patients1.zip