ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Eliminating duplicate records
http://www.istechforum.com/YaBB.pl?num=1220563060

Message started by David Waldmann on 09/04/08 at 14:17:40

Title: Eliminating duplicate records
Post by David Waldmann on 09/04/08 at 14:17:40

I thought I had this figured out once, however apparently it was something similar but different.

I am trying to make a mailing list from BKCMACCT, using BKCMACCL as a filter. The problem is that I get one Detail record for every instance of a class for a particular customer. For instance, if they have 4 classes they are listed 4 times. I've tried all sorts of different join and link types as I was sure that was the problem. It may be, but I haven't figured out the right combination yet...

I've also tried using "Select Distinct Records" but it almost doesn't make any difference (actually, it does remove two out of over 4000 total and probably many hundred duplicates).

Here is what I have:
BKCMACCT --> BKCMACCL, Left Outer Join (Not Enforced, Equal)

Selection Criteria:
not ({BKCMACCL.BKCM_ACCL_CLASS} in ["FL", "MW"])

Field in the report (as a bare minimum to test):
BKCMACCT.BKCM_ACCT_CODE

It does not pick any with either of those classes, it just picks one record for each customer that has one or more classes.

Any ideas?

TIA

Title: Re: Eliminating duplicate records
Post by Kelloggs on 09/04/08 at 15:26:19

SELECT DISTINCT
BKCMACCT.BKCM_ACCT_CODE,
BKCMACCT.BKCM_ACCT_NAME
FROM BKCMACCT LEFT JOIN BKCMACCL ON BKCMACCT.BKCM_ACCT_CODE = BKCMACCL.BKCM_ACCL_CODE
WHERE
(((BKCMACCL.BKCM_ACCL_CLASS)<>'FL'
And
(BKCMACCL.BKCM_ACCL_CLASS)<>'MW'))
OR
(((BKCMACCL.BKCM_ACCL_CLASS) Is Null))
ORDER BY
BKCMACCT.BKCM_ACCT_CODE

Try this sql on the pervasive control panel and see if that's what you are looking for

Kelloggs

Title: Re: Eliminating duplicate records
Post by David Waldmann on 09/05/08 at 03:30:20

That seems to work, but what do I do with it now?

I tried putting it in CR as a SQL Expression but it doesn't like it, and I have a bunch of other selection criteria as well, including some from BKARCUST...

Title: Re: Eliminating duplicate records
Post by Kelloggs on 09/05/08 at 07:25:48

SELECT DISTINCT
BKCMACCT.BKCM_ACCT_CODE,
BKCMACCT.BKCM_ACCT_NAME
FROM BKCMACCT LEFT JOIN BKCMACCL ON BKCMACCT.BKCM_ACCT_CODE = BKCMACCL.BKCM_ACCL_CODE
WHERE
BKCMACCL.BKCM_ACCL_CLASS <>'FL'
And
BKCMACCL.BKCM_ACCL_CLASS <>'MW'
OR
BKCMACCL.BKCM_ACCL_CLASS Is Null
ORDER BY
BKCMACCT.BKCM_ACCT_CODE

This one should work. It is plain and simple sql

Kelloggs

Title: Re: Eliminating duplicate records
Post by Kelloggs on 09/05/08 at 08:59:32

SELECT DISTINCT
BKCMACCT.BKCM_ACCT_CODE,
BKCMACCT.BKCM_ACCT_NAME
FROM BKCMACCT LEFT JOIN BKCMACCL ON BKCMACCT.BKCM_ACCT_CODE = BKCMACCL.BKCM_ACCL_CODE
WHERE
BKCMACCT.BKCM_ACCT_NAME Not Like 'TONG%'
AND BKCMACCL.BKCM_ACCL_CLASS <>'FL'
And BKCMACCL.BKCM_ACCL_CLASS <>'MW'
OR
BKCMACCL.BKCM_ACCL_CLASS Is Null
ORDER BY BKCMACCT.BKCM_ACCT_CODE

Note: BKCMACCT.BKCM_ACCT_NAME Not Like 'TONG%'

Kelloggs

Title: Re: Eliminating duplicate records
Post by David Waldmann on 09/05/08 at 11:01:52

I'm relatively new to CR11 and never used the "SQL Expressions" editor (the "Show SQL Query" is read only now). This is what I have:

http://i29.photobucket.com/albums/c275/dwaldmann/IS%20Tech/SQL-error.jpg

Am I even in the right place and/or missing something basic?

Title: Re: Eliminating duplicate records
Post by Kelloggs on 09/05/08 at 11:24:40

The sql code works on Pervasive. So the issue is on Cristal reports.

Sorry, I dont use CR, soo I cannt help you. But you can use MS Access or MS Excell to get your report.

:-/

Kelloggs

Title: Re: Eliminating duplicate records
Post by GasGiant on 09/05/08 at 12:40:08

The error box starts off with two SELECT statements then the error. Where is the second SELECT coming from? Does CR add one on its own? What happens if you leave off the one you pasted in?

Title: Re: Eliminating duplicate records
Post by David Waldmann on 09/05/08 at 13:08:57

GasGiant wrote:

The error box starts off with two SELECT statements then the error. Where is the second SELECT coming from? Does CR add one on its own? What happens if you leave off the one you pasted in?

I left out the one SELECT and the error message looks basically the same except it doesn't say "SELECT SELECT<<???>>DISTINCT" (that whole part is gone).

I do have Access and Excel but have never used Access, and Excel only occasionally.

Title: Re: Eliminating duplicate records
Post by kevind on 09/05/08 at 14:52:26

David,
A couple of things:

1. You do not want to put your Selection Criteria in a SQL Expression field. The reason for a SQL Expression field is so that you can use a database field as a Crystal report variable. For example, you may want a Crystal report variable to the the concatenation of a couple of text fields from the database. You would define a SQL Expression field that concatenates them so that in your report, you just refer to this new 'field'. The advantage is that the server will do the concatenation instead of crystal reports.

2. To do what you want to do, you use a group. You then put the fields that you want to show in the group header or footer sections. You suppress the detail section and the header or footer (which ever you did not use). What this does is it only creates one line for each individual record that the group is based on.

HERE (http://spectronics.com/dba/Crystal%20DISTINCT%20Sample.zip)is a simple report that illustrates this. his is Crystal 10, but XI should read it ok.

Set up your selection criteria using the Select Expert.

Title: Re: Eliminating duplicate records
Post by David Waldmann on 09/06/08 at 11:47:02

kevind wrote:

David,

2. To do what you want to do, you use a group.....What this does is it only creates one line for each individual record that the group is based on.

Almost sounds too simple, but makes perfect sense. Will give it a try on Monday.

Thanks!

Title: Re: Eliminating duplicate records
Post by David Waldmann on 09/08/08 at 04:10:26

Of course it works! Thanks so much.