return latest date

[Guest]

I have a table with patient chart#'s [Chart] and date of visits [Date].

I want to make a query that returns the [chart] but only the most recent
visit [date].

So basically I would get a list of patients and the last date they were seen.

What would be the best way to do this?

 

[Allen Browne]

Four approaches in this article:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

 

[John Welch]

Stefan-
The article Allen recommended is great if you need to get other info along
with just chart number and date, for example which doctor saw the patient on
that date. But if you just need a list of chart nums and last date seen, you
can use the query:

SELECT Chart, Max(VisitDate) AS [Last Seen On] FROM tblVisits GROUP BY
Chart;

This is the equivalent of 'Q1' in step 1 of the article.

By the way, you should avoid having fields named "Date" or anything else
that might get confused with special words used by Access.

hope this helps
-John