Compare records in a report

[mdafni]

Hi all,

I have a report that is based on two tables that are linked through a common field "policy no" (policy table and agent table). A policy can have more than one agent with different dates. In the report I want to show all the policies with the agents and in the case that a policy has two or more agents to show only the latest one and ignore the others.

Is there a way to do that?
Eg.
Policy Agent Date
100 01 1/1/05
100 02 5/1/05
120 06 5/1/05

In the report I only want to show
Policy Agent Date
100 02 5/1/05
120 06 5/1/05

Thanks in advance for all your help.
mdafni

 

[Madawc]

Group by policy. Sort by date within the group. Suppress the detail lines and show the same fields in the group footer.

It helps to give your Crystal version, since newer versions have extra options, and some extra problems.

Madawc Williams (East Anglia, UK)

 

[kskid]

Madawc forgot to mention that you want to sort by date descending to get the latest agent

 

[lbass]

Or you can group on {table.policy} and then go to report->edit selection formula->GROUP and enter:

{table.date} = maximum({table.date}, {table. policy})

Substitute your field names, of course.

-LB

 

[mdafni]

Thank you all for your help. It works fine.

Thanks
mdafni