Exclude record by date 1

[spiced]

I have a report CR7 report that uses dates from different tables. The first date is the Due Date which is between 7/18/05 and 10/31/05. The second date is the history date with a range between 7/18/04 and 7/7/05. In that history range some units have multiple entries. I want to eliminate the earliest history date entries from showing up and adding duplicate information i.e. T11005.

Hope this makes sense.
Thanks,
SPICED

FFS05 08/09/05 T - Temperature Scheduled
02/09/05
T11005 Thermocouples Calibration
GPMP 05-028

FFS05 08/09/05 T - Temperature Scheduled
08/10/04
T11005 Thermocouples Calibration
GPMP 05-028

FFS15 08/10/05 T - Temperature Scheduled
08/11/04
T11015 RTD'S Calibration
GPMP 05-038

FFS15 08/10/05 T - Temperature Scheduled
02/10/05
T11015 RTD'S Calibration
GPMP 05-038 Controller Calibration

 

[IanWaterman]

You probably need to give more info. What is the realationship between the two tables.

If you have a one to many relationship and you only want to show the last record of the table where you have many records.

Simply group on the field that relates the two tables, then order the data in ascending for the history dates. Place the data you want to see in the group footer. and suppress the detail lines. This will effectively show you the last record of that group.

Ian

 

[spiced]

If I understand relationship between the tables then it would be a common field "Equip ID" that relates the tables.

I'm not clear on how to do the grouping.

I can group on the Due Date which is what I want. If I was getting just the Due Date range the report would be about 3 pages but since the history range has to have a longer duration, because the calibration intervals differ, it includes each history "grouped" but makes the report 6 pages long.

 

[lbass]

Go to report->edit selection formula->GROUP and enter:

{table.historydate} = maximum({table.historydate},{table.duedate})

This assumes you have a group on {table.duedate}. My guess is that you might want a group #1 on equipment ID (if you are looking at dates for each piece of equipment), and then on due date, but I think the group selection formula would be the same, whether your groups are: equipID, duedate or just duedate.

-LB

 

[spiced]

Ian & LB,
Thanks for your help.

LB I entered the formula as shown and the report worked. It was also 2 pages shorter.

Thanks again,
SPICED