Records displaying Multiple Times 2

[kdawgzx6r]

Using a SQL database... I have an issue with records displaying multiple times. I am trying to show each recordID, then the earliest Contact_Date. However 99% of each recordID contains multiple Contact dates. I have writen a formula to show the earliest Contact_Date for each record ID, but when I generate the report the earliest contact_date is displayd however many times a particular record_ID has made a request in our database.

Example: If a particular record_ID has four different Contact_dates, say 02/01/2004,03/01/2004,04/01/2004 and 05/01/2004 my results look like this

Record ID Contact Date
6677 02/01/2004
6677 02/01/2004
6677 02/01/2004
6677 02/01/2004

How do I get my report to display ONLY the Record ID and the Earliest Condact Date ONCE?

 

[IdoMillet]

One approach is to simply Group by Contact ID, sort within the group by Date, so that earliest date is at the top, move all the columns you wish to show to the Group Header (which shows the info from the 1st row within the group), and suppress the detail section.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

It looks like you created a formula that was the minimum of Contact Date per group and then placed it in the detail section. Instead, you can do as Ido suggested, or you can go to report->edit selection formula->GROUP and enter:

{table.contactdate} = minimum({table.contactdate},{table.RecordID})

This assumes that you have grouped on {table.RecordID}.

-LB

 

[kdawgzx6r]

Ido,
Your way worked best. When I followed Lbass'es advice the date's changed to either display true or false....

Lbass, I would like to know if you have any other suggestions, as I feel that Crystal reports is quite capable of displaying data in the manner I wish to see it. Ido's way worked best for me right now However, I feel that it is a bit slopy (No offense Ido) to build reports in this manner.... Any suggestions would be greatly appreciated, as I am also working on letters that have the same issue, and cannot suppress the details pain as it messes up the letters I am working on generating.

THANKS AGAIN

 

[lbass]

The formula I gave you has to be created in the right place. Go to report->edit selection formula->GROUP and enter it there. Once you have done this, the report will only return the most recent date for each group.

-LB

 

[IdoMillet]

The only minor concern I have with LB's approach is that you would get multiple records per contact if several contacts occured on the first date.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[amiroux]

I'm running Crystal Reports 8.5 and on drill down would like the group header to show only ONCE at the top of each page.

At present, I have GH2 repeating every time there is a value change in GF2 - which is pretty ugly when there are many lines in GF2.

CR 9.0 solves this rather nicely. Any ideas on how do this with CR 8.5 or below?

Thanks.

amiroux

 

[IdoMillet]

I don't see the connection to this thread. Why not start a new one?

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com