Update Query

[Swi]

I have a table that a field named IM_Barcode_Digits that has *master* date in it.

I have another table that has transactional data in it that is fed by a scanner (can be multiple scans per ID). What I would like to do is update the scan date in the master table with the information from the transactional table with the most recent scan. The transactional table has the same field named IMB_Tracking_Code to match on and another field called Scan_Date among several other fields.

I assume the best way to do this would be with a correlated sub query? If so, could I have some assistance? I do not have much experience is Access.

Thanks.



Swi

 

[maxhugen]

Try this, it uses 2 queries (replace with your own table and field names):

Query: q_Update_MaxDate:

SELECT Max(Transaction.TransactionDate) AS MaxDate
FROM w_Transaction;

Query: q_Update:

UPDATE Master
SET Master.MasterDate = DLookUp("MaxDate","q_Update_MaxDate");


Max Hugen
Australia

 

[Swi]

So there is not a way to do this in one query? Also, I am probably going to want to pull over several other fields from the transactional table as well.

Thanks.

Swi

 

[maxhugen]

Hi Swi, no, I don't think this can be done in one query.

As far as getting other values from the transactional table, maybe you can incorporate these into the first 'grouping' query, and use additional DLookups to SET the other values in the master table. If not, you'll need to create additional support queries.

Max Hugen
Australia