Query Sum return incorrect total.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

idtjes3 - 18 Apr 2008 20:19 GMT

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?

Reply to this Message

Jerry Whittle - 18 Apr 2008 20:33 GMT

>> 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.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hello
>
[quoted text clipped - 9 lines]
>
> Any suggestions?

Reply to this Message

idtjes3 - 18 Apr 2008 21:04 GMT

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

> >> 1.049.23 + 1251.84
>
[quoted text clipped - 15 lines]
> >
> > Any suggestions?

Reply to this Message

NoodNutt - 19 Apr 2008 17:57 GMT

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.

Reply to this Message

idtjes3 - 21 Apr 2008 13:32 GMT

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.

> G'day idtjes3 (it's always nice to have a name here)
>
[quoted text clipped - 12 lines]
> HTH
> Mark.

Reply to this Message