Data display based on highest date

[Adrian Mundy]

I have a table based on a person test result, but as this is an on-going
test situation, I only need to display the latest information, but I need to
keep the data for reference,
Example table :-

bloggs Pass 12/4/05
bloggs Fail 7/3/5
smith pass 12/4/05

I only need to see
bloggs Pass 12/4/05
smith pass 12/4/05

If we do another test and add the following record
bloggs fail 20/4/05

I need to see
bloggs fail 20/4/05
smith pass 12/4/05

I have not found a way to display based on the latest date only. I know I
can select by a specific date, but that would not assist where we test
people on different days, and just showing the pass would not be suitable,
as we could have the same person passing multiple times.

Any assistance would be appreciated

regards

Adrian

 

[Douglas J. Steele]

Create a query that returns the name and maximum date:

SELECT MyName, Max(MyDate) AS MaxDate
FROM MyTable
GROUP BY MyName

Save that query as, say, qryMax, and then join it to the table:

SELECT MyName, MyGrade, MyDate
FROM MyTable INNER JOIN qryMax
ON MyTable.MyName = qryMax.MyName
AND MyTable.MyDate = qryMax.MyDate