Sum Total Problem

[Wolfroolz]

I am having an issue with what seems to be a very simple query and I would appreciate any advice into what is going wrong.

I have a query with two columns, budget type and dollar amount. When I run the query I get 44 records returned. When I copy that into excel I get a total of 147,134.29 with three different budget types. This is fine. When I use the sum totals however all the numbers are rounded down and I get 3 results, 1 for each data type with a total of 147,112. Which is the result I get if I round down all the records in excel. I tried using the totals with sum in the dollar amount column. I also tried using a formula Sum(Dollar amount) with Group By in the budget type. Both return the same results. I tried using the query wizard to create the query (in case I was messing something up when I did it manually) but also got the same wrong result. I changed the properties on the dollar amount column to currency, 2 decimal places but still only the whole dollars are being added up. I opened the query that this query is based on and also changed the column to currency with 2 decimal places, same result. I don't think it has anything to do with the column type because it works fine if I am not summing the results, but I wanted to try everything I could think of.

Has anyone had something like this happen to them?

Sample of Results from non Total Query
XM1 $3,486.60
XM1 $2,905.50
XM1 $23,898.25
XM1 $1,131.47
XM7 $16,902.19
XM7 $842.60
XM7 $842.60

Total Query
Expense $945.00
XM1 $66,407.00
XM7 $79,760.00

 

[arnelgp]

use decimal field type. you can also try using Cdec() function in your query:

put in a module:
Function NewCDec(MyVal)
NewCDec = CDec(MyVal)
End Function

now on your query:

Field: Total: NewCDec([Field])
Total: Sum

 

[Wolfroolz]

Thanks, appreciate your reply. I tried it and it does work, but I am unsure why I need to put in a function to set the type to decimal when the field in the table is decimal, currency. When all the queries have the field as currency and when the query displays the decimals as long as I don't total it. Why would a sum be whole integer? and Why would it drop all the decimals? I wouldn't even mind if the sum was shown as whole dollars but the value was added correctly, being up or down a dollar doesn't matter, but being off by all the change in the query does make a significant difference. I don't like to add extra complexity to a query for something that shouldn't need it.

 

[arnelgp]

if you google, decimal field type is the most appropriate when it comes to monetary value, rather than double or currency. i also recommend using decimal field type.

as to your question, check your table propety, if decimal field, is Scale set to 0 then set it to how many dec place you want to store.