asked on

MS Access - Update records with last date

Hello,
I have a table with multiple rows of records under the same IDs. There is probably around 20,000 different IDs with about 100'000 rows. What I would need to do is link two tables on these ID numbers, and then find only the newest records from that client. So I would just want to see who approved the account most recently, and on what date? Something like;

UPDATE tbl_Master INNER JOIN tbl_DB_Dump ON tbl_Master.SPN = tbl_DB_Dump.SPN 
SET tbl_Master.Approver = [tbl_DB_Dump].[Signature Name], 
tbl_Master.[Approval Date] = [tbl_DB_Dump].[Signature Time]
WHERE tbl_Master.[Approval Date] = NEWEST DATE.

Open in new window

Sander Stad

Try this:

SELECT 
  tbl_Master.Aprover, tbl_Master.[Approval Date]
FROM tbl_Master, 
  INNER JOIN tbl_Dump ON tbl_Master.SPN = tbl_Dump.SPN
ORDER BY tbl_master.[Approval Date] DESC

Open in new window