Solved

Access databse query and table update/append

Posted on 2011-03-09
8
331 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

734 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