Suppressing Duplicate records in Crystal

[jizzycrystal]

Hi
I have a consolidated report that consolidates based on a master nominal code and pulls all records from various databases based on parameters defined. (Sage) It works great except for one nominal account. For this nominal it is duplicating all the records from all databases.

I have tried the following:

1. In my view I have added the distinct command:
SELECT DISTINCT unique_no ,nlyear, posting_code, trans_period, journal_number, journal_desc, journal_date, journal_amount, analysis_code1, analysis_code2, analysis_code3, element3, element5, unique_no
FROM scheme.nltranm
WHERE (nlyear = '12') AND (journal_amount <> 0)

2. In my report I have added this formula:
IF PREVIOUS({nltran12_all.unique.no})= { nltran12_all.unique.no } THEN
TRUE
ELSE
FALSE

3.I formated the "unique no" record field to suppress duplicates (right click on it)& added a formula such as the above but still no joy.

4.I clicked on Database menu in Crystal and click “Select Distinct records" but still pulled in duplicate records

In the nlmastm table this nominal looks no diiferent to any other nominal.

Any idea's?
Thanks

 

[hilfy]

I suspect that if you look at the results of the view in a query tool that is not Crystal for the nominal that is causing a problem, you'll find that there is at least one field that has a different value - Distinct brings back a single row if the values in ALL of the selected fields are exactly the same - including the case of the letters unless you have your database set to be case-insensitive.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[jizzycrystal]

Hi Thanks for that but it is pulling the same record (which contains many fields)twice. Each record has a unique no. So I know it is pulling the same record twice as I can see the unique no field.When I execute the query in the database it doesn't duplicate...just in crystal. You might say it is my links but no other nominal has duplicate records being pulled could there be some problem with this master record?

 

[hilfy]

This is definitely a data issue of some sort. Have you run the full query from the report itself (Database, Show SQL Query) outside of Crystal?

If you have "Select Distinct Records" turned on, then there will be at least one field in the result set that is different between the "duplicated" records. The difference may be minor and easily overlooked, but it's there. You'll need to check ALL of the fields in the result set, not just the key fields.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[jizzycrystal]

How can it have different values if the two records have the same unique no?if I run the query from the server it only pulls each record once. I can't understand why it only happens to this nominal and all others pull each record only once Baffling!