Duplicate Records even after Selecting Distinct Records 1

[terenceb]

Hi ALL,

Iam running a Payment Report in Crystal 8.5 that pulls the data from a Cashe database. Under File and Report Options I have checked select disticnt records. However a few records are still duplicated. I looked into the database and these payment entries are singular. I was concerned that the payments may be breaking out into different payors, which may cause multiple records, that is no the case. I am joining three tables. Cash_Received(Primary table), client_demographics and tx_history_all. All have a equal join. And I have tried an left join. How can I further filter duplicate records.

 

[lbass]

I guess you have a join from Cash_Received to Client_Demographics and from Cash_Received to Tx_History_All? The use of an equal join or a left join depends on whether there is a record in the secondary table for each record in the primary table. If there isn't, you want to use a left join if you want the unmatched primary table field to be included in the report.

As a separate issue, if you have a one-to-many relationship between a primary and secondary table, the primary table field will repeat for each instance of the linked field in the secondary table. The Tx_History_All table might be responsible for this.

There are two potential ways of dealing with this. You can use running totals, so that you only sum {Cash_Received.amt} on change of a unique id field for that amount. Another approach would be to remove Tx_History_All from the main report and include it in a subreport which would be linked to the main report at the group, not the detail level--but this depends on how you need to use the Tx_History_All fields.

-LB

 

[Ngolem]

you have a join problem...perhaps showing us the structure of the tables and which fields are joined to what to us will result in someone seeing where the problem is.....your description is pretty sketchy as it stands.

Jim Broadbent

 

[synapsevampire]

Rather than speculating about solutions, everyone would be better served if teerenceb would post example data from each table, and expected output.

-k

 

[kenhamady]

The Select Distinct will not eliminate records if any field you are using is unique. The easiest way to see the fields being used is to go to Database - Show SQL Query and look at the fields in the SELECT clause. If you put all of those fields on the detail band you will probably find that your duplicates are not really duplicates, but have a different value for one of those fields.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[terenceb]

This is the type of output I am receiving:
As you can see client 4 appears twice with the exact same data, which is possible but not accurate in this case.

ENTRY POST POST SITE
DATE CLIENT NAME CODE PAYMENTS DATE SERVICE DATE CODE STAFF ID

10/06/2003 client1 20 827.55 09/10/2003 08/26/2003 10 000405
10/06/2003 client2 20 827.55 09/10/2003 08/20/2003 10 000405
10/06/2003 client3 20 827.55 09/10/2003 08/27/2003 10 000405
10/08/2003 client4 20 827.55 09/18/2003 08/22/2003 10 000405
10/08/2003 client4 20 827.55 09/18/2003 08/22/2003 10 000405
10/08/2003 client5 20 827.55 09/18/2003 08/21/2003 10 000405
This is the information that is in my SQL Query:

SELECT DISTINCT
cash_received.entry_date, cash_received.client_name, cash_received.amount, cash_received.posting_code, cash_received.service_code, cash_received.posting_date,
client_demographics.site_code,
tx_history_all.date_of_service, tx_history_all.STAFFID, tx_history_all.service_appt_status_code, tx_history_all.service_appt_status_value
FROM
SYSTEM.cash_received cash_received INNER JOIN SYSTEM.tx_history_all tx_history_all ON
cash_received.date_of_service = tx_history_all.date_of_service AND
cash_received.FACILITY = tx_history_all.FACILITY AND
cash_received.PATID = tx_history_all.PATID AND
cash_received.STAFF_ID = tx_history_all.STAFFID INNER JOIN SYSTEM.client_demographics client_demographics ON
cash_received.client_name = client_demographics.client_name AND
cash_received.FACILITY = client_demographics.FACILITY AND
cash_received.PATID = client_demographics.PATID
WHERE
cash_received.posting_date >= {d '2003-09-01'} AND
cash_received.posting_date <= {d '2003-09-30'} AND
(cash_received.posting_code <> '170' AND
cash_received.posting_code <> '160' AND
cash_received.posting_code <> '150')
ORDER BY
tx_history_all.STAFFID ASC,
cash_received.client_name ASC

I am not sure what additional information I should provide, please advice.

 

[kenhamady]

Your SELECT has 11 fields in it, but your detail only shows 8 of them. Add the other 3 fields on to the detail and you should see which field is not duplicated on these two records.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[terenceb]

Thanks to everyone who submitted responses. They all helped me think this through. I found a field in the cash_received table and the tx_history_all table that, once I link the two, helped me filter the duplicates.