?
Solved

Access databse query and table update/append

Posted on 2011-03-09
8
Medium Priority
?
334 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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 1500 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

743 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