Solved

Access databse query and table update/append

Posted on 2011-03-09
8
328 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:rreiss60
  • 5
  • 3
8 Comments
 

Author Comment

by:rreiss60
ID: 35086300
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.
0
 
LVL 39

Expert Comment

by:als315
ID: 35086303
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?
0
 

Author Comment

by:rreiss60
ID: 35087112
Here are the tables and forms.   Patients1.mdb
0
 

Author Comment

by:rreiss60
ID: 35089516
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 35092594
Try this example. Al is in one form - FormMainNew. Select patient and press button
Patients1.zip
0
 

Author Comment

by:rreiss60
ID: 35096968
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.
0
 

Author Closing Comment

by:rreiss60
ID: 35096986
Good solid try. Access file wouldn't run without error messages.
0
 
LVL 39

Expert Comment

by:als315
ID: 35097761
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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now