Calculating Currency Amounts

[Question Boy]

I am having trouble building a query to calculate

Sub-total
Regionial tax
Federal tax
Total

The first 3 are fine! but when I total them to calculate the Total it seems
to round the value so I can be off by a penny here and there. Below is the
actual SQL

SELECT
Sum(CCur([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice]))
AS [Sub-total],
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*[tbl_Invoice_Items]![Inv_Item_FedTaxRate]))
AS GST,
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*(1+[tbl_Invoice_Items]![Inv_Item_FedTaxRate])*[tbl_Invoice_Items]![Inv_Item_RegTaxRate]))
AS PST,
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])+(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*[tbl_Invoice_Items]![Inv_Item_FedTaxRate])+(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*(1+[tbl_Invoice_Items]![Inv_Item_FedTaxRate])*[tbl_Invoice_Items]![Inv_Item_RegTaxRate]))) AS Total
FROM (tbl_Customer_General_Info INNER JOIN tbl_Invoice_General_Info ON
tbl_Customer_General_Info.Cust_ID = tbl_Invoice_General_Info.Cust_Id) INNER
JOIN tbl_Invoice_Items ON tbl_Invoice_General_Info.Inv_Id =
tbl_Invoice_Items.Inv_Id;

Would anyone know how to resolve the rounding problem?

Thank you!!!

QB

 

[Marshall Barton]

I am having trouble building a query to calculate

Sub-total
Regionial tax
Federal tax
Total

The first 3 are fine! but when I total them to calculate the Total it seems
to round the value so I can be off by a penny here and there. Below is the
actual SQL

SELECT
Sum(CCur([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice]))
AS [Sub-total],
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*[tbl_Invoice_Items]![Inv_Item_FedTaxRate]))
AS GST,
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*(1+[tbl_Invoice_Items]![Inv_Item_FedTaxRate])*[tbl_Invoice_Items]![Inv_Item_RegTaxRate]))
AS PST,
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])+(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*[tbl_Invoice_Items]![Inv_Item_FedTaxRate])+(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*(1+[tbl_Invoice_Items]![Inv_Item_FedTaxRate])*[tbl_Invoice_Items]![Inv_Item_RegTaxRate]))) AS Total
FROM (tbl_Customer_General_Info INNER JOIN tbl_Invoice_General_Info ON
tbl_Customer_General_Info.Cust_ID = tbl_Invoice_General_Info.Cust_Id) INNER
JOIN tbl_Invoice_Items ON tbl_Invoice_General_Info.Inv_Id =
tbl_Invoice_Items.Inv_Id;

Would anyone know how to resolve the rounding problem?

I don't think it's a rounding problem. It sounds more like
a formatting problem. Try setting the fields' Format
property to something like $#,##0.0000

 

[Question Boy]

Sadly, it did not resolve my problem.

For instance, I have the following entry

Sub-total = 121.5000
Regionial tax = 6.0750
Federal tax = 9.5681

Now when invoiced it gives

Sub-total = 121.50
Regionial tax = 6.08
Federal tax = 9.57
But the Total is giving = 137.14 when in fact it should be giving 137.15

What I don't get is if each component of the total give the proper value,
why the Total summation does not?!

I did find a work around by adding the Round(,2) to each calculation but I
would mush prefer to actually understand the why behind the issue. Is it
improper table deisgn, query design or is this a normal solution?\

Thank you for your advice Marshall

QB

I am having trouble building a query to calculate

Sub-total
Regionial tax
Federal tax
Total

The first 3 are fine! but when I total them to calculate the Total it seems
to round the value so I can be off by a penny here and there. Below is the
actual SQL

SELECT
Sum(CCur([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice]))
AS [Sub-total],
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*[tbl_Invoice_Items]![Inv_Item_FedTaxRate]))
AS GST,
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*(1+[tbl_Invoice_Items]![Inv_Item_FedTaxRate])*[tbl_Invoice_Items]![Inv_Item_RegTaxRate]))
AS PST,
Sum(CCur(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])+(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*[tbl_Invoice_Items]![Inv_Item_FedTaxRate])+(([tbl_Invoice_Items]![Inv_Item_Qty]*[tbl_Invoice_Items]![Inv_Item_UnitPrice])*(1+[tbl_Invoice_Items]![Inv_Item_FedTaxRate])*[tbl_Invoice_Items]![Inv_Item_RegTaxRate]))) AS Total
FROM (tbl_Customer_General_Info INNER JOIN tbl_Invoice_General_Info ON
tbl_Customer_General_Info.Cust_ID = tbl_Invoice_General_Info.Cust_Id) INNER
JOIN tbl_Invoice_Items ON tbl_Invoice_General_Info.Inv_Id =
tbl_Invoice_Items.Inv_Id;

Would anyone know how to resolve the rounding problem?

I don't think it's a rounding problem. It sounds more like
a formatting problem. Try setting the fields' Format
property to something like $#,##0.0000

 

[John W. Vinson]

Sadly, it did not resolve my problem.

For instance, I have the following entry

Sub-total = 121.5000
Regionial tax = 6.0750
Federal tax = 9.5681

Now when invoiced it gives

Sub-total = 121.50
Regionial tax = 6.08
Federal tax = 9.57
But the Total is giving = 137.14 when in fact it should be giving 137.15

What I don't get is if each component of the total give the proper value,
why the Total summation does not?!

I did find a work around by adding the Round(,2) to each calculation but I
would mush prefer to actually understand the why behind the issue. Is it
improper table deisgn, query design or is this a normal solution?\

It's a normal situation. The individual calculations are being done to four
decimal places (if the fields are Currency datatype), or more (if they are
Single or Double). You're only *seeing* two places but the calculation is
actually being done with the full precision: the sum of those three numbers is
in fact 137.1431, which is displayed (rounded) to 137.14.

If you round each step of the calculation to the nearest cent, you get the
total agreeing with the subtotals, since there aren't the "extra" digits
hiding behind.

 

[Marshall Barton]

Sadly, it did not resolve my problem.

For instance, I have the following entry

Sub-total = 121.5000
Regionial tax = 6.0750
Federal tax = 9.5681

Now when invoiced it gives

Sub-total = 121.50
Regionial tax = 6.08
Federal tax = 9.57
But the Total is giving = 137.14 when in fact it should be giving 137.15

What I don't get is if each component of the total give the proper value,
why the Total summation does not?!

I did find a work around by adding the Round(,2) to each calculation but I
would mush prefer to actually understand the why behind the issue. Is it
improper table deisgn, query design or is this a normal solution?\

I thought you were originally asking why you were only
seeing two places, but now it seems like you are talking
about a rounding issue. These questions get intertwined
only when the values are displayed with less precision than
used in the calculations because something must be done
regarding the digits that are not displayed. There is no
such thing as a perfect way to deal with this issue (think
about writing the decimal value of 1/3 on a piece of paper).

Access uses the Round function to deal with this because it
is mathematically more accurate in the aggregate. If you do
not like the way the Round function does things, then you
can create your own rounding function or you can format the
result so it displays enough digits to make any rounding
insignificant.