Why is my aggregator query to count records giving me the result x

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

Guest

I know I'm forgetting something obvious. But I'm forgetting it completely and
can't remember no matter how hard I try.

I'm just trying to produce a reliable aggregator query to "group by" one
data element, and count (or sum) the contents of another field.

I keep trying different things and testing the results on a data element
known to total 104; however the answer table I get shows the total to be 108.
I have set the query properties to 'show unique values', and have mucked
with my 'joins' - trying them all possible ways, to see if I can even get
them to produce a *different* result (even if it's not *right*) . No go.

Is it obvious to anyone what I'm forgetting here? If so, *please clue me in*.
 
Post the relevant details of your source Table(s) and the SQL String of the
Query you tried.

--
HTH
Van T. Dinh
MVP (Access)



ms. viper scorpion said:
I know I'm forgetting something obvious. But I'm forgetting it completely and
can't remember no matter how hard I try.

I'm just trying to produce a reliable aggregator query to "group by" one
data element, and count (or sum) the contents of another field.

I keep trying different things and testing the results on a data element
known to total 104; however the answer table I get shows the total to be 108.
I have set the query properties to 'show unique values', and have mucked
with my 'joins' - trying them all possible ways, to see if I can even get
them to produce a *different* result (even if it's not *right*) . No go.

Is it obvious to anyone what I'm forgetting here? If so, *please clue me
Click to expand...
in*.
 
It is probably because the field you are counting on contains a NULL value.
Access ignores NULL values when performing aggregate functions. Try using
another column (your PK if you have one) as the field you count. Or,
instead of counting on a field, just enter a 1 in the Field row of the query
grid (Access will change this to read expr1:1) and sum on that column.

HTH
Dale
 
Back
Top