Query to Update Multiple Records Based on Current Record

[Newbold589]

I am trying to figure out how to allow a user to update many other records
based on data that was just entered on the current record.

Here is the scenario...

We're tracking charges entered against patient accounts. Each patient has a
unique account number and there is a unique record for every date of service.
So, the database will contain duplicate account numbers for each date of
service in which the patient was treated. For example...

Account: 123456 Date of Service: 5/1/2008
Account: 123456 Date of Service: 5/2/2008
Account: 345678 Date of Service: 5/1/2008
Account: 345678 Date of Service: 5/2/2008
Account: 345678 Date of Service: 5/3/2008

Suppose the user has entered charge "XYZ" on account 345678 for the 5/1/2008
date of service. Also, let's suppose that this particular patient received
the same service for each date of service contained within the database.

Is there a way that the user can click a button right after entering charge
"XYZ" on the 5/1/2008 record that will then apply that same charge to all
other dates of servcie for account 345678 (ie: the other two records in the
database)?

This isn't exactly what I am trying to accomplish but if I can get this done
I think I will be able to then figure out the rest of what I am trying to do.
However, the answer to this problem will be a very good start for me.

Thanks!

 

[Michel Walsh]

No need to update if you use query... and another table.


The 'other' table will basically get two fields:

Account Charge ' fields

345678 XYZ ' data sample



then, the query: bring the two tables (your original one plus the new one),
join them through their common Account field, and, in the grid, bring all
desired fields. That's all. You would be able to see the XYZ charge to all
rows where account=345678.



Hoping it may help,
Vanderghast, Access MVP