Query to Return Most Recent Date (1 Viewer)

[Princecoxy]

Hello All, I seem to be stuck on this query. A Customer can have multiple Interaction dates and Next Appointments which can have multiple entries by Date. I am trying to query the most recent Date for each NextAppointment per Customer.

So, looking at the below sample I would want to return 13-Mar-22 for that particular Customer and 21-Jul-22 for Customer with identification number 1234. These rows contain the most recent Date per NextAppointment.

Any thoughts on how I can accomplish this? Thanks.

 

[June7]

Seem to need a simple aggregate query. SELECT CustomerID, Max(InteractionDate) AS MDate FROM table GROUP BY CustomerID;

If you want to return entire record, review for one way http://allenbrowne.com/subquery-01.html#TopN

 

[Pat Hartman]

If there are multiple interaction types, you might need criteria to specify which type.

SELECT CustomerID, Max(InteractionDate) AS MDate FROM table GROUP BY CustomerID
Where CustomerID = Forms!myform!myCustomerID AND InteractionType = "NextAppointment";

 

[June7]

WHERE clause comes before GROUP BY.

 

[Pat Hartman]

Thanks for the correction. I wasn't paying attention.