| Author |
Message |
lions_share
Newbie
Joined: 23 Mar 2007
Location: United States
Online Status: Offline
Posts: 4
|
   Topic: Deleting duplicate records
Posted: 26 Apr 2007 at 8:46am |
|
I am in the process of designing a report to filter customers by mailing address. Some of our customers have several family members listed on their account with the same mailing address. Right now if we have a bulk mailing then some of our customers will get 3 or 4 brochures due to duplicate addresses in the database.
I grouped the data by mailing address and chose 'Suppress if Duplicated' in the format editor. This suppresses the data but is there a way to eliminate the record from the report entirely? For example in the lower right the record count may be 9500 and I want to get rid of the records that contain duplicate addresses to bring the count to 8900 or whatever.
Any help is appreciated. I'm running CR 9 from a SQL Server DB using ODBC.
Thanks.
|
 IP Logged |
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 26 Apr 2007 at 8:52am |
Have you tried going to the Database menu and turning on Select Distinct Records? I'm not sure if this will work in your case, though - it won't if you include the customer name and multiple names have the same mailing address.
Also, the count at the bottom of the screen is not always the most accurate way of determining how many records are on the report - if any of your record filtering happens in Crystal instead of on the database, there will be more records read that will actually appear on the report. A better way of determining how may records are actually on the report would be to include a DistinctCount of the mailing address field in the report footer.
-Dell
|
|
|
|
IP Logged |
|
lions_share
Newbie
Joined: 23 Mar 2007
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 26 Apr 2007 at 9:11am |
|
I did in fact try the Select Distinct Records but it did not work for the reason that you indicated. Thank you for your reply.
|
|
IP Logged |
|
vivekvarma
Newbie
Joined: 03 May 2007
Location: India
Online Status: Offline
Posts: 6
|
Posted: 03 May 2007 at 1:30am |
hi,
use distinct in your query and select top1 .this may help ur requirement
|
|
regards,
vivek varma
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 03 May 2007 at 6:23am |
vivekvarma,
Distinct won't work in this situation - the addresses are the same but the names that go with them aren't.
-Dell
|
|
|
|
IP Logged |
|
EscApe
Newbie
Joined: 13 Nov 2006
Location: Finland
Online Status: Offline
Posts: 37
|
Posted: 07 May 2007 at 4:09am |
|
Have you tried grouping on sql command:
select max(name), address, city, state, max(country)
from table db1
where expressions
group by address, city, state
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 07 May 2007 at 7:49am |
EscApe,
You're answer will work if you're using CommandText. However, Crystal doesn't allow CommandText for all databases. The way to get Crystal to handle this in all situations is, as I stated above, set a group on the address and then put all of the information you need in either a group header or footer.
-Dell
Edited by hilfy - 07 May 2007 at 7:50am
|
|
|
|
IP Logged |
|
|
|
