How to include the first occurence of a record

[aarohivi]

I have table with the following columns (this is just a sample since my table has many more columns) and the sample data below:

Order_No|Customer_No|Order_Date(YYYYMMDD)
1 |A001 |20010131
2 |A002 |20021231
3 |A001 |20020324
4 |A002 |20030115
5 |A001 |20040221

I want to create a report where the detail section will look as follows:

Order_No|Customer_No|Order_Date(YYYYMMDD)|Date First Ordered
1 |A001 |20010131 |20010131
2 |A002 |20021231 |20021231
3 |A001 |20020324 |20010131
4 |A002 |20030115 |20021231
5 |A001 |20040221 |20010131

How do I get crystal to find the minimum (first) date the customer ordered? thanks for your help.

 

[mrmtek]

what version of crystal are you using?

 

[lbass]

There's probably a way of doing this using arrays, but another approach is to insert a subreport, and in the subreport, group on {table.CustNo}. Then insert a summary(minimum) on the date field and then suppress all subreport sections except the group footer. Link the subreport to the main report on {table.CustNo} and then place the subreport in the details section of the main report. Placing a subreport in the details section will slow the report, but it might be the simplest solution to this problem.

-LB

 

[GJParker]

If you have access to the database i would recommend creating a view of the data, containing Customer_No and Min(Order_Date), Then link this into your report.

Gary Parker
Systems Support Analyst
Manchester, England

 

[aarohivi]

Thanks for all your messages. In the meantime I have proceeded as Gary suggested to solve my problem - which is to create a view with the aggregate column. This was the most simple and elegant solution.

Regards,
Vinay