Query is showing invalid totals (due to multiple records)

[ohi]

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

Here is the query in question:

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.

 

[Galaxiom]

The problem is you are expecting the wrong answer.
Access is returning exactly the results that it should.
It counted two records and it is going to show the sum of those two records. The records don't cease to exist just because they are not displayed due to the grouping.

It sounds like you need to group first and then use another query to sum the results of the grouping.

 

[spaddhu]

I don't know if this is what you want. Try giving the 'distinct' clause if you don't want double counting.
ie. count(distinct qry_cfu_HD.Size) as ...

 

[ohi]

I don't know if this is what you want. Try giving the 'distinct' clause if you don't want double counting.
ie. count(distinct qry_cfu_HD.Size) as ...

That is what I am looking for I need what the values should be, and not doubled. With that I'm getting an error. (img attached)

The problem is you are expecting the wrong answer.
Access is returning exactly the results that it should.
It counted two records and it is going to show the sum of those two records. The records don't cease to exist just because they are not displayed due to the grouping.

It sounds like you need to group first and then use another query to sum the results of the grouping.

I should have mentioned I'm horrible at sql and advanced queries, which is why I'm asking for help.

I'm aware it's giving me what I'm requesting of it, I don't know how to fix it so that it will give me what I'm looking for (the actual count and not doubled records).

Sorry I wasn't more clear.

 

[spaddhu]

hey.. sorry for the late reply. was out on personal accounts..
hope the error is solved by now.. if not..

from your screenshot i could see only one possibility of error. try putting count(DISTINCT [TableName]![ColumnName]).. note the use of the square brackets.

 

[ohi]

Thanks for the reply, but I'm still getting the same error

This is the code

SELECT qry_cfu_MainSelect.[Case Number], qry_cfu_MainSelect.Examiner, Count(DISTINCT [qry_cfu_HD]![Size]) AS CountOfSize1, Count(DISTINCT [qry_cfu_LooseDrives]![Size]) AS CountOfSize2, Sum(DISTINCT [qry_cfu_HD]![Size]) AS SumOfSize1, Sum(DISTINCT [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;

Maybe I did something wrong, I dont know.

 

[ohi]

After some trial and error I finally got this to work properly

SELECT qry_MainSum.[Case Number], qry_MainSum.Examiner, qry_MainSum.[Date of Assignment], qry_LooseDriveSums.CountOfSize, qry_LooseDriveSums.SumOfSize, qry_HDSums.CountOfSize, qry_HDSums.SumOfSize
FROM (qry_MainSum LEFT JOIN qry_LooseDriveSums ON qry_MainSum.[Case Number] = qry_LooseDriveSums.[Case Number]) LEFT JOIN qry_HDSums ON qry_MainSum.[Case Number] = qry_HDSums.[Case Number];

Thanks for all your help.