Query Sum return incorrect total.

[idtjes3]

Hello

basically i have a query that pulls about six different totals from
another query and is "suppose" to add them all together. Ive tried setting
the "Total;" method in design view to "Sum" the column but its WAY off. Ive
also tried creating an expression which returns the same incorrect value.
Heres my numbers im bringing in and what Access is telling me the total is.
Maybe someone can see what its actually doing.

Sum: 1.049.23 + 1251.84 + 104.32 + 208.64 + 52.16 + 80.71
Access is returning 629'040.1 as the Sum

Any suggestions?

 

[Jerry Whittle]

1.049.23 + 1251.84

The first one is using a period as a thousand seperator. What is the
Regional Setting for your computer? You might try formatting the numbers
without a thousand seperator.

 

[idtjes3]

im sorry that was a typo. its suppose to be a comma

 

[NoodNutt]

G'day idtjes3 (it's always nice to have a name here)

In your example:

Sum: 1.049.23 + 1251.84 + 104.32 + 208.64 + 52.16 + 80.71

Is this what you are writing as your expression?

In the query grid try something like this:

MySum: = Nz([yourfirstfield])+Nz([yoursecondfield]).....etc

set it to SUM.

HTH
Mark.

 

[idtjes3]

Hello NoodNutt,

Actually all the values i wish to add are in the same field. I think I
found what the problem is. For some reason it seems to be adding all the
values in the field correctly but then its multiplying the sum by however
many entries there are. So say my equation is Nz(sum([Amount]),0) and the
values are 4+6+1+9+, its giving me 80 as the answer. ( 20 x 4 ).

Anyways i just bypassed the problem by "Summing" on the actual report
instead of in the query. I just wanted to have the least amount of clutter in
the query as possible. It would be nice to know what the problem is as Ive
actually run into this snag a few times. Id like to know how to fix it in the
future or to avoid it all together.