Sum Calculation Incorrect

[Jackson via AccessMonster.com]

Hi,

I've got a problem that I don't even know where to start for fixing it. I
have a Query, qryPortfolio which has a field MVbase. Another field Type can
be 'Equities' 'Cash' or 'Options'. If I filter on Options and group
everything to just get one figure for the sum of MVbase, it comes up with a
figure that is incorrect, as in, if I export the data to excel, excel comes
up with the correct sum but access won't.

It seems it's leaving out certain numbers (though I can't narrow it to exact
records...) all the numbers involved are doubles so I can't work out why this
is happening...any ideas what could possibly cause this to calculate
incorrectly? Does the same incorrect calculation as Sum on reports too....

Thanks.

 

[Guest]

How much off are the two calculations? How do you know that Excel is correct
and not Access.

Many double precision numbers wil contain round-off errors. Do you have the
same computational problem when you filter on one of the other "Type" values?

Dale

 

[Jackson via AccessMonster.com]

HI Dale,

Thanks for your reply. The figures are off by around 1.3 million (similar
amounts over two days of data) so not a rounding issue. The other ones work
fine for Type and calculate correctly, I know excel is correct becaused added
them up manually too. If I display the records in the query or report and
then add them up manually I get what excel gets, it's literally only
malfunctioning when aggregating into a sum...its really strange...

How much off are the two calculations? How do you know that Excel is correct
and not Access.

Many double precision numbers wil contain round-off errors. Do you have the
same computational problem when you filter on one of the other "Type" values?

Dale

Hi,

[quoted text clipped - 11 lines]

Thanks.

 

[Guest]

Have you done a group by on the "Type" field to see whether you have an
extraneous space in the field values? I've run into that before when people
had the option of entering data that was not in a list. A space before or
after the text would make the difference.
--
Email address is not valid.
Please reply to newsgroup only.

HI Dale,

Thanks for your reply. The figures are off by around 1.3 million (similar
amounts over two days of data) so not a rounding issue. The other ones work
fine for Type and calculate correctly, I know excel is correct becaused added
them up manually too. If I display the records in the query or report and
then add them up manually I get what excel gets, it's literally only
malfunctioning when aggregating into a sum...its really strange...

How much off are the two calculations? How do you know that Excel is correct
and not Access.

Many double precision numbers wil contain round-off errors. Do you have the
same computational problem when you filter on one of the other "Type" values?

Dale

Hi,

[quoted text clipped - 11 lines]

Thanks.