Calculation of total incorrect

[PhilJ]

Hi all,


I have a problem with a query, but am posting in the general forum as I suspect the issue may be more to do with issues in datasheet fields.

In my query, I want to do a basic sum of a cost field per invoice/receipt. I made a query and the outcome is nearly right, but not quite. It seems that the query does not take into account anything past the decimal point when it is performing the sum.

Going back to the original table, the full number is displayed, but I noticed in design view that the scale property is set to 0. I can change this to 2, to try to get it to save the number after the decimal point, but even after saving the table, opening it again shows that it has reverted to 0. This is not a read-only issue and it happens despite changing the result type to decimal and the format to fixed. Does anyone have any ideas about how I might get the right result from my query? Could it be something to do with the fact that the field in the table uses a Calculated datatype? Any help would be gratefully appreciated!

[davegri]

Check everything for an integer data type occurring somewhere.

[PhilJ]

Thanks - I just have the one field involved in the calculation and this was set to long integer. I changed it to decimal and specified a scale of 2 with decimal places also as 2, but the same thing happens when I go back to my calculated fields - I can set these as scale 2 but whenever I save and close and reopen, I find it has gone back to 0.

[davegri]

Where is the sum being displayed? On a form, on a report, being stored in a table? Can you post the query SQL?

[PhilJ]

The sum is in a query, just to give me the totals of two fields. The SQL is:
SELECT subReceiptInvoiceItems.ReceiptID, Sum(subReceiptInvoiceItems.ItemCostPreGSTCalcTotal ) AS SumOfItemCostPreGSTCalcTotal, Sum(subReceiptInvoiceItems.ItemCostIncGSTCalcTotal ) AS SumOfItemCostIncGSTCalcTotal
FROM subReceiptInvoiceItems
GROUP BY subReceiptInvoiceItems.ReceiptID;

[davegri]

A precision of 2 means that a decimal number can be no greater than 2 digits. (44 or 4.4 or .44)
A scale of 2 indicates 2 decimal places.
Try changing your precision to something like 8, or whatever the max number of digits including the decimal in the sums might be.

[PhilJ]

That did it. Thank you!