Solved

Access databse query and table update/append

Posted on 2011-03-09
8
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 40

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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
 
LVL 40

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 40

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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