Selecting latest record by date field

[malk]

I have a database with multi records for each person. Each entry has a date with it. I want the report to only show the latest record for each person. How would I write this selection?

 

[dcm]

malk: Build a running total to find the maximum of date fro each person, You can then logically suppress any record where date<>running total David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[malk]

do you mean less then or equal?

 

[dcm]

Malk: Not equal David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[malk]

Thanx, but I have a few issues:

1- Suppress just prints a blank report and leads to ugly reports.

2- I have to put this formula into every field on the report otherwise it prints.

3- The date field is no longer printing after the first time.

Any other suggestions?

 

[dcm]

Malk: Let's just re-visit things. If all you want is the latest record per person then group your report by person, sort your records into ascending date order and place the date field on the Person group footer line (you'll get just the latest date displayed for each person).

If however, you want to select other data based upon the latest date per person then my suggestion of building a running total of the maximum of the date and resetting per person will enable you to identify the latest date even without sorting them (you'll still have to group by person). This running total could then be used to select the appropriate record(s) as in:

if {Date_passed_exam} = {#RTotal0} then {Exam name} else &quot;&quot;

This would select the latest exam passed etc.. David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Seagate Enterprise Partner