Query is showing invalid totals (due to multiple records)

Ohi

Joined
Nov 14, 2009
Messages
5
Reaction score
0
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.
 

Attachments

  • sample.jpg
    sample.jpg
    15.3 KB · Views: 107

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top