Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Data Duplication Issue Post Reply Post New Topic
Author Message
crobertson81
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Location: United States
Online Status: Offline
Posts: 3
Quote crobertson81 Replybullet Topic: Data Duplication Issue
     Posted: 30 Sep 2009 at 12:18pm
I am currently having an issue linking two tables in a report.  When I generate the report, the records in the detail section become duplicated.  To further explain, in one table I am looking for item numbers, transaction date and transaction quantity. 
In the next, I am looking for item numbers, transaction dates, and shipment quantities.
In the last I am retrieving item numbers and descriptions. 
The only fields that seem to be consistent between tables are the item numbers, so I am joining on that basis.
 
However, what ends up happening is this:
Item #   Date       Trans Qty      Date        Ship Qty
1001 (Grouping)
              5/12/09     49000       5/20/09     20000
              5/12/09     49000       5/28/09     12000
              6/1/09       30000       5/20/09     20000
              6/1/09       30000       5/28/09     12000
 
2001 (Group)
              5/12/09     20000       5/5/09       20000
              5/12/09     20000       5/19/09     12000
              5/12/09     20000       6/5/09       15000
 
If you know why this is happening, or better yet, a way to fix it, I would greatly appreciate your help.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Sep 2009 at 12:53pm
Sounds like you have 3 tables not 2 and you have Table1 linked to Table 2 and Table1 linked to Table3.
Can you change you link to Table1 to table2 and table2 to table3?


Edited by DBlank - 30 Sep 2009 at 12:54pm
IP IP Logged
crobertson81
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Location: United States
Online Status: Offline
Posts: 3
Quote crobertson81 Replybullet Posted: 30 Sep 2009 at 1:01pm
Oops sorry, I do have three tables. 
 
I have tried different combinations of links with just links between the tables such as
 
Transactions---->Descriptions------>Shipments and
Descriptions---->Transactions------>Shipments
 
with all causing this repetition, but in different ways. 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Sep 2009 at 1:05pm
Check the raw data per table and find which of these tables has 2 rows per item number. Then figure out whcih row on that table you need and how can you identify that "correct row" consistently per itemnumber. You will have to discard the rows via a select statment or a command or a another data source process (like a SQL view or stored proc).
IP IP Logged
crobertson81
Newbie
Newbie
Avatar

Joined: 30 Sep 2009
Location: United States
Online Status: Offline
Posts: 3
Quote crobertson81 Replybullet Posted: 30 Sep 2009 at 1:53pm
Both the shipment and transaction tables have multiple rows per item number.  One row per transaction.  I would like to capture this information, but I dont like it cycling. 
 
It appears to look through table A, match the item record in table A to all records in B that have that item #, find the next record of that item in table A and rematch to the records already contained in the report. 
 
I just want a collection of all transactions in each table found of each item so if there were 5 transaction in table A and 2 in table B it would show as:
 
Item
     Table A                          Table B
       GL Date      Qty             GL Date     Qty
       A1               A1              B1              B1
       A2               A2              B2              B2
       A3               A3              null
       A4               A4              null
       A5               A5              null
 
I hope I am explaining myself well and not being too dense.
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Sep 2009 at 2:15pm
For display purposes you can just suppress duplicate records even though they are pulled into the report. However you also are indicating a NULL need here so you likely need a left outer join between your A and B (and maybe A and C tables?).
Looks like you want it grouped on ITEM.
If you place the other fields on the details section and sort them correctly you will likely see a pattern of a Primay Key Field (likely the PK from table B or C) that repeats on your Dupe Rows.
You can use that to conditionally suppress the dupe rows using the section expert and a suppress formula on detail section:
next(PK field)=PK field
 
Just remember that suppressed rows are still calculated in SUMS, counts, averages, etc unless you specifically exclude them via formulas or running totals.
Does this do what you want?


Edited by DBlank - 30 Sep 2009 at 2:16pm
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.