Solved

MS Access - Update records with last date

Posted on 2009-05-14
6
357 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:andyb7901
  • 4
6 Comments
 
LVL 9

Expert Comment

by:Sander Stad
ID: 24382785
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

0
 
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?

Kelvin
0
 

Author Comment

by:andyb7901
ID: 24382861
Yeah pretty much
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 22

Accepted Solution

by:
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



Kelvin
0
 
LVL 22

Expert Comment

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


Kelvin
0
 
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

Kelvin
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

809 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