Different SUM in report and query (1 Viewer)

[Hello1]

Hello everyone,

I have some pretty weird rounding problem or I'm just not seeing something.
The situation is next. I have one table which keeps those amounts I need displayed on the report, and the field I'm fighting with is the tax one.
Every record has a value > 0 in the tax column, there's total of 1975 records. When I do a SUM with a query I get 78910.28. In the report query I use something like If Costumer doesn't Pays tax and is from area x show tax, otherwise put a 0.
I have 3 of that kind but different area:
(IIf([RegistrovanPDV] = False And [Entitet]='F',[IznosPDV],0)
(IIf([RegistrovanPDV] = False And [Entitet]='S',[IznosPDV],0)
(IIf([RegistrovanPDV] = False And [Entitet]='B',[IznosPDV],0)

I made sure that every customer has an area of those 3 offered, so that shouldn't be a problem.

And I have one more for when the customer pays tax IIf([RegistrovanPDV] = True, [IznosPDV], 0).

RegistrovanPDV has the value of either True or False, there's no Null, I checked.

Now all of that was probably not really necessary to describe but anyways.
When I SUM all those fields with a query I also get 78910.28, however on the report it shows 78909.13, that exact same query.
Now to the weird thing, when I change the query just from SELECT to SELECT TOP 2000 (it takes the same records because there are 1975 of total) I get the right amount, 78910.28.
EDIT: Found out that another important factor in the query for it to show the correct amount is the order, I added the same order which is present on the report design.

Not sure if I have described it well, if needed I can put more details.
Anyone had something like this?
Thanks!

 

[The_Doc_Man]

Just remember that (for reasons not fully known), Access reports strip down parts of your query that you supplied to drive the report and in particular lose the grouping and sorting. You have to use the report design system to re-establish proper grouping and sorting. Therefore, it is POSSIBLE that part of your problem is that the report trashed your original query.

 

[Ranman256]

is all your math IN the query? (it should be)

 

[Hello1]

Just remember that (for reasons not fully known), Access reports strip down parts of your query that you supplied to drive the report and in particular lose the grouping and sorting. You have to use the report design system to re-establish proper grouping and sorting. Therefore, it is POSSIBLE that part of your problem is that the report trashed your original query.

I will try to export my query into a temporary table and then use that same table to run my report.

is all your math IN the query? (it should be)

Yup, only math I run on the report is the SUM in report footer

 

[Hello1]

The temporary table fixed it, however, when executing the append query I had to include the ORDER BY, without it it still messes up the rounding. Mysterious ways of MS Access
Will leave it like this for now.. Thanks for the help

 

[Pat Hartman]

I will try to export my query into a temporary table and then use that same table to run my report.

NO!!!! That just adds bloat.

Whatever your calculation is, it is dependent on record order so include the order by in the query. That will work the same way the temp table does without the other problems caused by database bloat.

 

[The_Doc_Man]

Pat beat me to it because I was out for a haircut. Don't export queries to temporary tables. Instead, be aware that Access diddles with the query you provide as a recordsource. (No, don't ask me why. Because they can. Because it is Microsoft. Because the phase of the moon. Nobody knows, but they do it.) The correct solution is to make the report have the sorting and grouping that matches whatever you did with the query that worked for you. THOUGH changing to a CURRENCY data type might also help, since if the problem involves rounding, you eliminate that situation by using CURRENCY, which is actually a scaled integer data type.

 

[Hello1]

NO!!!! That just adds bloat.

Whatever your calculation is, it is dependent on record order so include the order by in the query. That will work the same way the temp table does without the other problems caused by database bloat.

I did that before but it didn't work, unless I include "SELECT TOP (then a random number just to be bigger than the actual number of records)". Somehow I didn't like that, I could put TOP 1000000, it will never reach that number of records but still.. No idea.. I have the .accde made for each user, so it shouldn't really make much of a bloat, that report is maybe used 5 times a month.

Pat beat me to it because I was out for a haircut. Don't export queries to temporary tables. Instead, be aware that Access diddles with the query you provide as a recordsource. (No, don't ask me why. Because they can. Because it is Microsoft. Because the phase of the moon. Nobody knows, but they do it.) The correct solution is to make the report have the sorting and grouping that matches whatever you did with the query that worked for you. THOUGH changing to a CURRENCY data type might also help, since if the problem involves rounding, you eliminate that situation by using CURRENCY, which is actually a scaled integer data type.

Hope the haircut turned out good
I don't have any grouping in the query, nor the report. The order I do but I already tried that.
As for currency, my database is on SQL Server, the data type of those currency felids is float. I tried changing to decimal, and rounding all the records to 2 decimal places but it didn't help either.
Unfortunately I don't have much time to experiment more with it, I will have to go with the tmp table for now, or that SELECT TOP x