CODEWITHSUNDEEP
ms-access
Asynchronous
Asynchronous

Reputation: 3977

How do I return a single record using max function and aggregation on table with different values

I have a single table that looks like this:

enter image description here

I need to return the row with the most recent LastVisit Date as highlighted above from a single table.

I tried using the MAX function on the date but the problem is, I cannot aggregate the other columns because the values are not the same, this returns all three rows.

How do I get only the latest row highlighted?

Upvotes: 1

Views: 2085

Answers (1)

HansUp
HansUp

Reputation: 97101

You can use DMax to get the most recent LastVist date.

DMax("LastVisit", "YourTable")

Then to get the row (or rows) with the same date, use that DMax expression in a query WHERE criterion.

SELECT *
FROM YourTable
WHERE LastVisit = DMax("LastVisit", "YourTable");

Another way to do it would be to use a subquery instead of DMax to get the latest date.

SELECT *
FROM YourTable
WHERE LastVisit = (SELECT Max(LastVisit) FROM YourTable);

Upvotes: 1

Related Questions