How to get the latest date value from multiple records? 1

[gegeying]

Here is my question:
2 tables: equipment, workorder
they are linked: equipment.eqnum = workorder.eqnum

for each eqnum, there could be >1 workorder.

Fields of <workorder> table like WORKORDER, EQNUM, ACTDATE, METERREADING
Fields of <equipment> table like EQNUM, EQTYPE,EQLOCATION


On the report, it need to show EQNUM,EQLOCATION. For each EQNUM, the report needs to show the latest date (ACTDATE has to be the latest one) that equip is used and the corresponding METERREADING showing on that equip.


How to do that?

 

[dgillz]

Sort the report so that the most current value is on the top of each group. Then drag the relevant fields into the group header, and hide the details section.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[gegeying]

Data example:
in <WORKORDER> table:

[WO] [EQNUM] [ACTDATE] [METERREADING]
WO1 EQ001 02/01/2006 1000
WO2 EQ002 03/21/2006 200
WO3 EQ001 05/02/2007 3000
WO4 EQ003 06/10/2005 4000
WO5 EQ003 09/12/2006 6000

in <EQUIPMENT> table:
[EQNUM] [EQTYPE] [EQLOCATION]
EQ001 T D1
EQ002 S D2
EQ003 S D2

On the report, it should shows like below:

D1
EQ001 T 05/02/2007 3000

D2
EQ002 S 03/21/2006 200
EQ003 S 09/12/2006 6000


My question is how can I get the latest ACTDate from WORKORDER table for each EQNUM?

Thanks.

 

[lbass]

Insert a second group on equipno and then go to report->selection formula->GROUP and enter:

{workorder.actdate} = maximum({workorder.actdate},{equipment.equipno})

-LB

 

[gegeying]

LBass,
thanks for your quick reply. But here comes another problem, how to show corresponding METERREADING for the latest ACTDATE?

i.e
latest_date = maximum({workorder.actdate}, {equipment.equipno})
I create another formula to show meterreading;
latest_meter = maximum({workorder.meterreading},@latest_date)

error comes up for latest_meter formula. what should I do?

I am using Crystal Report 8.5.

Thanks.

 

[lbass]

You are entering the formula in the wrong place. Go to report->edit selection formula->GROUP and enter it there. Then the entire record for the most recent date will be displayed.

-LB