Results 1 to 9 of 9
  1. #1
    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.

  2. #2
    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.

  3. #3
    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.

  4. #4
    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.

  5. #5
    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    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

  7. #7
    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.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    Can you post the SQL you're working with?
    George
    Home | Blog

  9. #9
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •