Aggregate returning 4 "non-aggregate" records.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

A simple aggregate Query is Grouping on 4 Fields (one Field uses a
Lookup..but I see NO "duplicates" in that).

Then I run "sum"s on several Long Integer Fields.

Result = a few (4) "duplicate aggregate values".

Any known Access Bug for this?

TIA - Bob
 
As far as I understand, that is perfectly ok.


Name Score
Mary 1
Mary 3
Ann 4



then


SELECT name, SUM(score)
FROM myTable
GROUP BY name



will lead to a duplicated sum of scores:


Mary 4
Ann 4


The only bug I am aware is when you have an indexed field not allowing
duplicated values but allowing nulls, then, with a GROUP BY on that field,
Jet blindly use that index, making the result with multiple NULL groups.
(While two nulls are not equal, they are not distinct either, so only one
group with a null should appear in the result). MS SQL Server does not have
this bug, since it does not allow multiple NULL in an index without
duplicated values.



Vanderghast, Access MVP
 
Michel...

Your thoughts below got me to examine the 3 Fields I was Grouping on.

I've inherited this DB, and noticed one of the Fields wasn't providing any
useful info.
I removed it from the Query, and Volia..the problem is gone. Not sure why,
but it solves this problem.

Thank you - Bob
 
Back
Top