Select Only Newest Date 1

[ddpleasant]

I am running Crystal Reports V8.5 on a Windows NT box. Need a way to elliminate duplicate records and keep only those with the highest EnterDAteTime. I am trying to create a Benefits Report that shows for each employee the following table:

EmpId EmpName HireDate BenStrtDt EmplyePay EmplyrPay
0001 J Smith 08/24/03 08/24/03 62.50 128.30
0002 A Smith 01/24/03 05/14/03 62.50 128.30

Because there are incorrectly entered duplicate records in the table I get Duplicates as such.

EmpId EmpName HireDate BenStrtDt EmplyePay EmplyrPay
0001 J Smith 08/24/03 08/24/03 62.50 128.30
0001 J Smith 08/24/03 08/24/03 62.50 128.30
0002 A Smith 01/24/03 05/14/03 62.50 128.30
0002 A Smith 01/24/03 05/14/03 62.50 128.30


The only field that is different is EnterDateTime. So I need to retain only those records in each case wherre it is the MAX(EnterDateTime).

Please advise.

Thank you!

 

[Madawc]

Group on something like EmpId. Use [Sort Records] under [Reports] to put them into date-time order, ascending or descending. If you then suppress details and show date/time in the header and footer, this will be minimum and maximum. Use whichever suits you.

Running totals can be set to sum just once per group, or to use a formula that tests the date/time against the maximum for that group.

Madawc Williams (East Anglia, UK)