If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Crystal Reports > Eliminating Duplicate Records With Linked Tables

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 5
Eliminating Duplicate Records With Linked Tables

Hello All, I'm looking for some guidance.

This problem has been plaging me for years.

I always get duplicate records on records from database Alpha (for example) when trying to narrow down database Alpha's records by looking into Linked database B.

For example:

Database "Orders" is joined to Database Vendors by field "Vendor Name"

I want to show all orders when vendors.zipcode="90210"

Every time I try to report on this I get duplicate records. Doesn't matter the scenerio, and it doesn't matter the fields.

I will set up selection criteria to only show zipcodes in 90210.

How can this be eliminated?

Thanks for any advice.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jun 2005
Posts: 27
I don't know if this will help, but if there are multiple rows in the second table that relate to a row in the first table, the row from the first table will repeat once for each unique value from the second table. The solution is to put the info from the second table that is repeating into a subreport, and link the subreport to the first table, which is in the main report.

So, for example, say you have a vendor, which has 3 order records in their own table related to this vendor. All the info about the vendor from the vendor table will probably repeat once for each of those three records unless you put them in a subreport. Or at least, that's what I've found to happen with me when I have two tables linked together, one of which has more than one row for each row fro the other one.

I'm not entirely sure this is what's happening for you, there are other reasons that things repeat, but maybe it'll help.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 5
What you said is very similar to what I'm experiencing. The problem is that I don't want to show the parent record if there are no sub records.

So I don't want to list Vendor Records if there are no "orders" placed to that vendor. If I make a subreport I will not be able to summarize and control the parent display.

I try and put the detail records in a group header and that gives me limited results. It requires a tremendous amount of tinkering to get it to work smoothly.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 5
Just wanted to follow up with this...

The best way to avoid these duplicates is to add groups and put the detail lines in the group header. I then usually hide the detail line.

Works great. Takes some tinkering but I've always gotten it to work.

Just wanted to share since I figured this out.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
Just a thought on this one - it sounds to me like there is more than one record for each "Vendor Name" in the Vendor table and so that is why you are getting duplicates.

I would want to find out the root cause of why and try creating a silly little report just on the Vendors table grouping by Vendor Name and showing all of the data set (ie all columns from the table). If I insert a count (not a distinct count, though) on the "Vendor Name" field which is evaluated on each Group and then set a group selection criteria to ignore any groups where the count is only 1. This should show where duplicates are occuring. Then I would study the results to spot a field that I can filter on so that I only get one line out per vendor and put that into my selection criteria in the originial report and then the duplicates may disappear.

Does this sound anything like your original problem?

I have another idea involving the unique field if you are using SQL and then a running total, but that's much harder to deliver.

HTH
Caspar
Reply With Quote
  #6 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,047
If I've understood correctly (I'm not a Crystal user) the problem lies with the JOIN between the two tables. More on SQL JOINs can be found here.

If you want to simply identify duplicates then run this query against your database (syntax may vary depending on database).
Code:
SELECT MyField
FROM MyTable
GROUP BY MyField
HAVING Count(*) > 1
Hope this is of some help
__________________
George
Home | Blog
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Oct 2005
Posts: 5
Here we are two years later and this problem continues to plague me to no end.

Every time I try and reference fields that are linked to a table which is linked to another table I get duplicate recoreds. I've tried every possible join combination I can think of in the current situation I'm working on and I can't get the duplicates to go away.

I've also tried to improve the join links to no avail.

I would appreciate if any experienced Crystal users could explain to me thier reasoning when the do table joins. I am not an expert with the whole "Join from"; "Join to" and enforcement states even though I've read the helpfile at length.

If anyone could offer advice on preventing duplicates in CR I would be forever in your debt.
Reply With Quote
  #8 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,047
Can you post the SQL you're working with?
__________________
George
Home | Blog
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2007
Posts: 54
Which version of Crystal are you using?

What is the data you are reporting on stored in? Is it a SQL database? Or an Excel spreadsheet? Or what?

With regard to joins, an equal join means the data must be in both tables, whereas an outer join means the data is in one table, but does not need to be in the other.

So if I have Table 1 containing "A", "AA", "AB", "BB" and I link that field to Table 2 which contains values "A", "AB", "D", "E" in the corresponding field. With an equal join, only the entries showing "A" and "AB" will appear in the report. If I use a left-outer join from Table 1 and then report on the entries I will get "A", "AA", "AB", "BB" from Table 1 and I will get "A", <NULL>, "AB", <NULL> from Table 2. On a report the <NULL> will just appear as a blank or empty space. If you start using these fields in formulae then yiou have to think carefully about what you are doing and what you want.

Coming back to your duplicate records, there must be a reason for it and knowing the underlying data structure will help. If I get anything like this then I write the simplest possible reports with just the 2 tables at question and try and zero in on one set of results that exhibit the problem and then start hunting through ALL of the fields to try and spot what is duplicating and how I can find a better unique join. Is it still the Orders and Vendors tables that you are wrestling with?

Hope the above helps some.
Good night,
Caspar
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On