Multiple Records but only display most current

  • Thread starter Thread starter Adrian Mundy
  • Start date Start date
A

Adrian Mundy

I have a report requirement where I have a set of test result per person,
but all that is relevant is the latest by date. I know I could delete the
non-relevant, but these are needed for history. My examples would be

Able 23/11/04 Pass
Able 26/03/05 Fail
Able 28/04/05 Pass
Bravo 23/11/04 Fail
Charlie 23/11/04 Pass
Charlie 23/11/05 Pass

All I require to see is

Able 28/04/05 Pass
Bravo 23/11/04 Fail
Charlie 23/11/05 Pass

Any assistance would be appreciated.

regards
Adrian
PS if you saw similar to this in just access group, I apologise, as I did
not know the report group existed
 
Adrian said:
I have a report requirement where I have a set of test result per person,
but all that is relevant is the latest by date. I know I could delete the
non-relevant, but these are needed for history. My examples would be

Able 23/11/04 Pass
Able 26/03/05 Fail
Able 28/04/05 Pass
Bravo 23/11/04 Fail
Charlie 23/11/04 Pass
Charlie 23/11/05 Pass

All I require to see is

Able 28/04/05 Pass
Bravo 23/11/04 Fail
Charlie 23/11/05 Pass
Click to expand...


This needs to be done in the report's record source query.

SELECT T.person, T.testdate, T.result
FROM table As T
WHERE testdate = (SELECT Max(X.testdate)
FROM table As X
WHERE X.person = T.person)
 
Back
Top