MS Access - Update records with last date

Posted on 2009-05-14
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
  • 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

LVL 22

Accepted Solution

Kelvin Sparks earned 500 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now