Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Unanswered: Query is showing invalid totals (due to multiple records)

    I know what the problem is, but I'm not sure how to fix it.

    Here is the query in question:
    Code:
    SELECT qry_cfu_MainSelect.[Case Number], qry_cfu_MainSelect.Examiner, Count(qry_cfu_HD.Size) AS CountOfSize1, Count(qry_cfu_LooseDrives.Size) AS CountOfSize2, Sum(qry_cfu_HD.Size) AS SumOfSize1, Sum(qry_cfu_LooseDrives.Size) AS SumOfSize2
    FROM (qry_cfu_MainSelect INNER JOIN qry_cfu_LooseDrives ON qry_cfu_MainSelect.[Case Number] = qry_cfu_LooseDrives.[Case Number]) INNER JOIN qry_cfu_HD ON qry_cfu_MainSelect.[Case Number] = qry_cfu_HD.[Case Number]
    GROUP BY qry_cfu_MainSelect.[Case Number], qry_cfu_MainSelect.Examiner;
    and there is also a visual sample attached.

    The problem is this. There are multiple records in the table and it's doubling the counts instead of showing the true totals.

    Case Number 09100 and 09102 are showing correctly, however 09101 is incorrect.

    CountOfSize 2 (second column) is 2 and should be 1
    Sum of Size2 (4th column) is 8 and should be 4. It's doubled because there are 2 records attached to case number 09101.

    I really hope I made sense of all of this. Anyone have a solution for me? Any help is greatly appreciated! TYIA.
    Attached Thumbnails Attached Thumbnails sample.jpg  

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I might FIRST make a unique query base on what records you uniquely want to total.

    (you could even make this a make-table query to make a temp table.)

    Then design your totals query off of that query (note: you may need to even separate these queries into 1 or more queries.) Keep in mind that you also want to keep the key record (perhaps the autonumber or primary key field) so that when you join them together, you'll get a good totals query.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    That's what I already had done. Each of those queries are based off the PK (which is the case number) and the fields I need calculated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you worked out why you have duplicate data.. thats what I'd be more worried about. putting in a fix to the summation query is a kludge workaround and thats never good, never clever and never practiocal in the long run.

    if your basic data model is right then these things don't occur later on.

    it maybe painful to fix after the event, but get it fixed, unless you are leaving the company fairly soon. its not nice if you have to revisit the app in say 6 months or later, its not nice for the person following on from you. make these sort of kludgey workarounds and this app will never ever leave you alone. it will stalk you for the rest of your career with that company
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2009
    Posts
    4
    Quote Originally Posted by healdem View Post
    so have you worked out why you have duplicate data.. thats what I'd be more worried about. putting in a fix to the summation query is a kludge workaround and thats never good, never clever and never practiocal in the long run.

    if your basic data model is right then these things don't occur later on.

    it maybe painful to fix after the event, but get it fixed, unless you are leaving the company fairly soon. its not nice if you have to revisit the app in say 6 months or later, its not nice for the person following on from you. make these sort of kludgey workarounds and this app will never ever leave you alone. it will stalk you for the rest of your career with that company
    It's not duplicate data in the tables, it's duplicate data in the query. (I should have mentioned I'm absolutely horrible with SQL, and the problem could and more than likely lay with the them.) The tables have distinct data, the query will have multiple due to the fact everything is linked by case number and there are several other tables linked to that by the case number. Sometimes there are multiple records of a subtable linked to the main table, this is how I'm getting duplicates.

    I also attached a link to the relationships of the tables so you can see what I mean.
    Attached Thumbnails Attached Thumbnails tables.jpg  
    Last edited by Ohi874; 11-25-09 at 13:26.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You could always edit the queries properties (show the properties for the query) and set the Unique Records (or Unique Values) property to YES. For this to work though, you have to make sure there isn't a field in the query which doesn't have unique values (ie. a recordID or autonumber type field)


    Although as healdem pointed out, you may want to look closely as to reason why you're getting duplicated records. Sometimes it's valid, othertimes not.
    Last edited by pkstormy; 11-27-09 at 04:40.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2009
    Posts
    4
    As I explained there is a valid reason for the duplication of records, but thanks for the assistance.

Posting Permissions

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