Getting Dublicate Records, Please help!

[Stella1209]

My report connnected to Oracle Database. All tables are linked logically. When I run the report it has a lot of dublicate records. What should I do to resolve that problem?

 

[MalcolmW]

Are the duplicate records completely the same? If so, Database|Select Distinct Records is a way of making up for what is usually (but not always) incomplete or incorrect table linking. Malcolm

 

[Stella1209]

Malcolm,
Thanks for your responce.
I checked my links, it seems to be correct. Could you tell me where I should put Select Distinct Records, Should I change the SQL Statement manually?
Thanks,
Stella

 

[MalcolmW]

from the main menu,
Database|Select Distinct Records Malcolm

 

[IdoMillet]

It sounds like you didn't expect to get those
duplicate records, which would indicate to me
that your joins are wrong.

One possible "suspect" is that you joined a table
to two other tables it has One-to-Many relationships
with. If this doesn't isolate the problem for you,
please describe the tables you are joining.

Cheers,
- Ido

 

[emcevoy]

hello

To suppress duplicate data simply RIGHT CLICK on your field and click on FORMAT FIELD. Once in the properties dialogue box, go to the COMMON TAB. Place a check in the box beside SUPPRESS IF DUPLICATED. Click OK at the bottom of the page and your data should no longer be duplicating. I hope that this helps point you in the right direction. E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com

 

[Stella1209]

E. McEvoy,
Thanks for your idea, but I tried to do that already, and it's not what I need because all suppressed records were counted in Totals.
Stella

 

[emcevoy]

hi there

What you can do for your totals if they are running totals is to make manual conditional running totals to exclude these. For duplicate records the formula would look like this***
Whileprintingrecords;
If onfirstrecord or previous({fieldname})<>{fieldname} then
Numbervar X:= X + 1

***For Conditionally Suppressed Records***
You will have to include the suppression condition in the formula for example:
Lets say you have record #51 suppressed then the formula should look like this:
If {fieldname} <> 51 then numbervar X:=X+1
This means the running total will only add one for every record as long as it is not 51.

The Display Formula
Place this formula in the group footer to display the result
Whileprintingrecords;
Numbervar X;

I hope this helps. E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com

 

[Stella1209]

Thanks Guys, I created a formula in Detail section for suppressing duplicates, now I got the right data:

WhilePrintingRecords;
If Previous ({C_ILSOBG.SO}) = {C_ILSOBG.SO} and Previous ({C_ILSOBG.ITEM}) = {C_ILSOBG.ITEM} and Previous ({CCN.CCN}) = {CCN.CCN} then true

But, I still have a problem with a Totals.
E. McEvoy,
Thanks for your help, but that formula is not working in my case, I need to calculate the total of the values, not the count of the records: For example the total for QTY field that have values:
1
13
4
5
Total: = 23
I am still getting all suppressed values counted in Totals.
Please, help me if you have any other ideas.
Thanks in advance,
Stella

 

[MalcolmW]

It doesn't sound like you've resolved the problem of &quot;duplicate&quot; records, but are merely suppressing their display.
When something of this nature happens, it is reasonable to ensure your joins are correct. If they are, you have two options - the select distinct method (which only works if the records are completly duplicate), or conditional summaries (which are a bit more complicated).
As you are asking for help on conditional summaries, I suggest that you first ensure the simpler solutions don't resolve the issue (checking the joins, select distinct).
Malcolm

 

[Stella1209]

Malcolm,
I am using CR V5, and I don't have an option for select distinct. Any idea what I should do?

Thanks,
Stella

 

[emcevoy]

Hi there

Here is a link to seagate software's Dupes.rtf.

http://support.seagatesoftware.com/communityCS/TechnicalPapers/dupes.exe

&quot;This document explains how duplicate records arise from table linking, and provides tips on suppressing duplicate records from appearing on the report and from being included in totals calculations. Applies to version 5 and 6 of Seagate Crystal Report Designer and Seagate Info Report Designer.&quot;

This should solve your issues.




E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com

 

[emcevoy]

Hi there

Here is a link to seagate software's Dupes.rtf.

http://support.seagatesoftware.com/communityCS/TechnicalPapers/dupes.exe

&quot;This document explains how duplicate records arise from table linking, and provides tips on suppressing duplicate records from appearing on the report and from being included in totals calculations. Applies to version 5 and 6 of Seagate Crystal Report Designer and Seagate Info Report Designer.&quot;

This should solve your issues.




E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com