Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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

0
andyb7901
Asked:
andyb7901
  • 4
1 Solution
 
Sander StadCommented:
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
 
Kelvin SparksCommented:
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
 
andyb7901Author Commented:
Yeah pretty much
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Kelvin SparksCommented:
Select tbl_Master.SPN, tbl_Master,Approver, Max(tbl_Master.[Approval Date] FROM tbl_Master GROUP ON tbl_Master.SPN



Kelvin
0
 
Kelvin SparksCommented:
Not sure what that other table was being used for


Kelvin
0
 
Kelvin SparksCommented:
You'll probably need a group on for Approver

GROUP ON tbl_Master.SPN, tbl_master.Approver

Kelvin
0

Featured Post

Industry Leaders: 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!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now