MS Access - Update records with last date

Posted on 2009-05-14
Medium Priority
Last Modified: 2012-06-27
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

Question by:andyb7901
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
  • 4

Expert Comment

by:Sander Stad
ID: 24382785
Try this:

  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

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24382807
Why using an update when you want to see records. Are you just looking for the most recent data from tbl_Master for each SPN?


Author Comment

ID: 24382861
Yeah pretty much
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 22

Accepted Solution

Kelvin Sparks earned 2000 total points
ID: 24382916
Select tbl_Master.SPN, tbl_Master,Approver, Max(tbl_Master.[Approval Date] FROM tbl_Master GROUP ON tbl_Master.SPN

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24382925
Not sure what that other table was being used for

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 24382934
You'll probably need a group on for Approver

GROUP ON tbl_Master.SPN, tbl_master.Approver


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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