• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

MS Access - Deleting Top Record

To whom it may concern,

In the attached file, I need to run a query on Table 1 that deletes the most recent record (as denoted by the date) for EACH NAME and returns Table 2 as a result.

Any ideas would be appreciated.

-Maroulator
Example.xlsx
0
maroulator
Asked:
maroulator
  • 4
  • 3
1 Solution
 
mbizupCommented:
Try this to see if it returns the correct data:


SELECT t.[Name], t.Title, T.Salary, t.[Date]
FROM Table1 t
INNER JOIN
(SELECT [Name], MAX([Date]) AS MaxDate
FROM Table1 
GROUP BY [Name]) q
ON t.[Name] = q.[Name] AND t.[Date] = q.[MaxDate]

Open in new window



If that yields the results you need, use the wizard to change the query type to a Make Table query to put the records into Table2.


(Right-click in the query window --> Query Type --> Make Table query)
0
 
mbizupCommented:
<< I need to run a query on Table 1 that deletes the most recent record (as denoted by the date) for EACH NAME and returns Table 2 as a result. >>

Just an aside... I think there is a little discrepancy in what you are asking here.

The results shown in table2 are from keeping the most recent record, not deleting it.

My post above shows you how to *keep* that most recent record.

If you really do need to delete the most recent record, use the wizard to turn the above query into a DELETE query instead of a Make Table query.
0
 
maroulatorAuthor Commented:
This doesn't yield what I need; the query you gave me pulls out the records corresponding to the most recent dates. These are the records that I need deleted.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
maroulatorAuthor Commented:
Understood! Thanks for catching that! I will try again and let you know.
0
 
maroulatorAuthor Commented:
Thanks; for some reason, Access is telling me that I need to specify the table from where I want the records deleted, and I think I am doing that when I am running your query. I haven't used a delete query in the past. Am I missing smth?

Could you send me the syntax for the delete query? Perhaps that could help.
0
 
mbizupCommented:
Give this a try...


Save this as a query called "qryDeleteThese"


SELECT [Name], MAX([Date]) AS MaxDate
FROM Table1 
GROUP BY [Name]

Open in new window


And run this DELETE query:

DELETE *
FROM Table1
WHERE [Name] & [Date] IN (SELECT [Name] & [MaxDate] FROM qryDeleteThese)

Open in new window

0
 
maroulatorAuthor Commented:
Thanks!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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