ISTech Support Forum - Eliminating duplicate records

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1924
Gender: male

Eliminating duplicate records
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

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: Eliminating duplicate records
Reply #1 - 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

Back to top

« Last Edit: 09/04/08 at 22:47:22 by Kelloggs »

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Eliminating duplicate records Reply #2 - 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...
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: Eliminating duplicate records
Reply #3 - 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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

Kelloggs

Active Member

Do crazy people know
they are crazy?

Posts: 785
Gender: male

Re: Eliminating duplicate records
Reply #4 - 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

Back to top

Evo ERP - 35 Users
Dumped MS Access like a hot potato (VB.Net rules!!!)

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Eliminating duplicate records Reply #5 - 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: Am I even in the right place and/or missing something basic?
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

Kelloggs Active Member Do crazy people know they are crazy? Posts: 785 Gender:	Re: Eliminating duplicate records Reply #6 - 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
Back to top	Evo ERP - 35 Users Dumped MS Access like a hot potato (VB.Net rules!!!) IP Logged

GasGiant Administrator How can I help? Posts: 1523 Gender:	Re: Eliminating duplicate records Reply #7 - 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?
Back to top	IP Logged

David Waldmann

Active Member

Live to work, or
work to live?

Posts: 1924
Gender: male

Re: Eliminating duplicate records
Reply #8 - 09/05/08 at 13:08:57

Quote from 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?

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.

Back to top

David N Waldmann
President
Vermont Hardwoods
Chester, VT

Evo-ERP, 5 user
IST Build: 3/4/19, patched 04/30/19
Pervasive v11.31
Server 2012 / Win10 x64
Crystal Reports v11

IP Logged

kevind

Active Member

I was more than
willing to
"pony up",
how b'out U?

Posts: 639
Gender: male

Re: Eliminating duplicate records
Reply #9 - 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 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.

Back to top

Kevin Damke
Spectronics Corporation
ISTECH 2004.1 7/26/13 SP1 - 20 user
Evo-ERP Build 7/25/13 T7 -7i R6 - 3 user
(Prev version was 2004.1 10/24/12)
(All Patches Installed as of 7/26/13)
Pervasive 2000i SP4 - Crystal Reports V10

IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Eliminating duplicate records Reply #10 - 09/06/08 at 11:47:02 Quote from kevind on 09/05/08 at 14:52:26: 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!
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged

David Waldmann Active Member Live to work, or work to live? Posts: 1924 Gender:	Re: Eliminating duplicate records Reply #11 - 09/08/08 at 04:10:26 Of course it works! Thanks so much.
Back to top	David N Waldmann President Vermont Hardwoods Chester, VT Evo-ERP, 5 user IST Build: 3/4/19, patched 04/30/19 Pervasive v11.31 Server 2012 / Win10 x64 Crystal Reports v11 IP Logged