• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 592
  • 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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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