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
Solved

Access databse query and table update/append

Posted on 2011-03-09
8
329 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 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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