Report totals off by a couple cents.

J

JMS

Gross Order Total and Sales tax Total in Main report are off by a couple
cents.


----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing 4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which seem to
calculate fine...I think. The calculations are incorrect when I Sum those 2
in the Report footer. This is where I am seeing it off by 1 or 2 pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No
 
T

Tom Ellison

Dear JMS:

Not unlikely, the amounts in the column that is exhibiting this behavior
contain amounts with fractions of a cent. For example:

1.234
2.342
3.451

This adds up to 7.027, which will display as 7.03. However, the amounts
will display as:

1.23
2.34
3.45

This adds up to 7.02. A penny off, due to rounding.

Now, is it the designed intention here to allow this amount to be entered
beyond 2 decimal places?

If so, then for it to appear to add up correctly, you must round each value
before you add them.

If not, then your data entry must restrict this to 2 decimal places. Use an
Input Mask. If a user attempts to type a third place to the right of the
decimal point, it will ignore that.

You may also want to use an update query to fix up the values already
stored.

Before you change any stored values, you may want to report them to the
users. If someone has entered 123.445 it may be they meant to type 123.45.
If you drop the fraction and leave 123.44, then you will not have fixed the
problem. It may be best to give the users an opportunity to fix their
mistakes, rather than you "fixing" them incorrectly.

Tom Ellison
Microsoft Access MVP


JMS said:
Gross Order Total and Sales tax Total in Main report are off by a couple
cents.


----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax
Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing 4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which seem
to
calculate fine...I think. The calculations are incorrect when I Sum those
2
in the Report footer. This is where I am seeing it off by 1 or 2 pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No
Click to expand...
 
J

JMS

Hi Tom:
Thanks for the reply first off. The amounts are being generated by the
sum expression below that calculate unitprice and salestax so users only
need to put the unit price of the item in the Order Details Table. The line
totals in the report seem to calculate and display properly but the grand
totals are off by a few pennies. The data type for unit price and sales tax
rate is set to currency and 2 decimal places and will not allow input mask
changes. The Main Report Query gross Order Total shows a number like
667.3768 Thanks again


Tom Ellison said:
Dear JMS:

Not unlikely, the amounts in the column that is exhibiting this behavior
contain amounts with fractions of a cent. For example:

1.234
2.342
3.451

This adds up to 7.027, which will display as 7.03. However, the amounts
will display as:

1.23
2.34
3.45

This adds up to 7.02. A penny off, due to rounding.

Now, is it the designed intention here to allow this amount to be entered
beyond 2 decimal places?

If so, then for it to appear to add up correctly, you must round each
value before you add them.

If not, then your data entry must restrict this to 2 decimal places. Use
an Input Mask. If a user attempts to type a third place to the right of
the decimal point, it will ignore that.

You may also want to use an update query to fix up the values already
stored.

Before you change any stored values, you may want to report them to the
users. If someone has entered 123.445 it may be they meant to type
123.45. If you drop the fraction and leave 123.44, then you will not have
fixed the problem. It may be best to give the users an opportunity to fix
their mistakes, rather than you "fixing" them incorrectly.

Tom Ellison
Microsoft Access MVP


JMS said:
Gross Order Total and Sales tax Total in Main report are off by a couple
cents.


----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS [Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100 AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax
Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS
CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing 4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which seem
to
calculate fine...I think. The calculations are incorrect when I Sum those
2
in the Report footer. This is where I am seeing it off by 1 or 2 pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No
Click to expand...
Click to expand...
 
J

JMS

The amounts are being generated by the
sum expression below that calculate unitprice and salestax so users only
need to put the unit price of the item in the Order Details Table. The line
totals in the report seem to calculate and display properly but the grand
totals are off by a few pennies. The data type for unit price and sales tax
rate is set to currency and 2 decimal places and will not allow input mask
changes. The Main Report Query gross Order Total shows a number like
667.3768 Thanks
Tom Ellison said:
Dear JMS:

Not unlikely, the amounts in the column that is exhibiting this behavior
contain amounts with fractions of a cent. For example:

1.234
2.342
3.451

This adds up to 7.027, which will display as 7.03. However, the amounts
will display as:

1.23
2.34
3.45

This adds up to 7.02. A penny off, due to rounding.

Now, is it the designed intention here to allow this amount to be entered
beyond 2 decimal places?

If so, then for it to appear to add up correctly, you must round each
value before you add them.

If not, then your data entry must restrict this to 2 decimal places. Use
an Input Mask. If a user attempts to type a third place to the right of
the decimal point, it will ignore that.

You may also want to use an update query to fix up the values already
stored.

Before you change any stored values, you may want to report them to the
users. If someone has entered 123.445 it may be they meant to type
123.45. If you drop the fraction and leave 123.44, then you will not have
fixed the problem. It may be best to give the users an opportunity to
fix their mistakes, rather than you "fixing" them incorrectly.

Tom Ellison
Microsoft Access MVP


JMS said:
Gross Order Total and Sales tax Total in Main report are off by a couple
cents.


----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS
[Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100
AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax
Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS
CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning
Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing 4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which seem
to
calculate fine...I think. The calculations are incorrect when I Sum
those 2
in the Report footer. This is where I am seeing it off by 1 or 2
pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No
Click to expand...
Click to expand...
Click to expand...
 
J

JMS

Can anyone help please?

The amounts are being generated by the
sum expression below that calculate unitprice and salestax so users only
need to put the unit price of the item in the Order Details Table. The
line
totals in the report seem to calculate and display properly but the grand
totals are off by a few pennies. The data type for unit price and sales
tax
rate is set to currency and 2 decimal places and will not allow input mask
changes. The Main Report Query gross Order Total shows a number like
667.3768 Thanks
Tom Ellison said:
Dear JMS:

Not unlikely, the amounts in the column that is exhibiting this behavior
contain amounts with fractions of a cent. For example:

1.234
2.342
3.451

This adds up to 7.027, which will display as 7.03. However, the amounts
will display as:

1.23
2.34
3.45

This adds up to 7.02. A penny off, due to rounding.

Now, is it the designed intention here to allow this amount to be
entered beyond 2 decimal places?

If so, then for it to appear to add up correctly, you must round each
value before you add them.

If not, then your data entry must restrict this to 2 decimal places.
Use an Input Mask. If a user attempts to type a third place to the
right of the decimal point, it will ignore that.

You may also want to use an update query to fix up the values already
stored.

Before you change any stored values, you may want to report them to the
users. If someone has entered 123.445 it may be they meant to type
123.45. If you drop the fraction and leave 123.44, then you will not
have fixed the problem. It may be best to give the users an opportunity
to fix their mistakes, rather than you "fixing" them incorrectly.

Tom Ellison
Microsoft Access MVP


Gross Order Total and Sales tax Total in Main report are off by a
couple
cents.


----Order Details Table----
OrderDetailID - Primary Key - AutoNumber - Long Integer
OrderID - Text
ProductID - Text
Quantity - Number - Double - Decimal 0
UnitPrice - Currency - Format Currency - Decimal 2
CCSPrice - Currency - Format Currency - Decimal 2
Discount - Currency - Format Currency - Decimal 2


----Order Table----
OrderID - Text
CustomerID - Number - Long Integer - Decimal Auto
EmployeeID - Long Integer - Decimal Auto
OrderDate - Date/Time - Short Date
PurchaseOrderNumber - Text
ShipName - Text
ShipAddress - Text
ShipCity - Text
ShipStateOfProvince - Text
ShipPostalCode - Text
ShipCountry - Text
ShipPhoneNumber - Text
ShipDate - Date/Time - Short Date
ShippingmethodID - Number - Long Integer 0 Deciaml Auto
FreightCharge - Currency - Decimal 2
CityTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
StateTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
SalesTaxRate - Currency - Format Percent - Deciaml 2 - Default value 0
ARDetailID - AutoNumber - Long Integer

----Main Report Query----
SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID,
Sum(CCur([Quantity]*[UnitPrice])*100)/100+Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100+First(CCur([FreightCharge])*100)/100
AS [Gross Order Total], Sum(CCur([Quantity]*[UnitPrice])*100)/100 AS
[Net
Order Total], Sum(CCur([Quantity]*[UnitPrice]*[SalesTaxRate])*100)/100
AS
[Sales Tax Total], Orders.FreightCharge,
Sum(CCur([Quantity]*[UnitPrice]*[CityTaxRate])*100)/100 AS [City Tax
Total],
Sum(CCur([Quantity]*[UnitPrice]*[StateTaxRate])*100)/100 AS [State Tax
Total], Sum(CCur([Quantity]*[CCSPrice]-[Discount])*100)/100 AS
CCSPricing,
Orders.OrderDate,
Sum(CCur([Quantity]*[UnitPrice])*100)/100-Sum(CCur([Quantity]*[CCSPrice])*100)/100+Sum(CCur([Discount])*100)/100
AS Profit
FROM Orders LEFT JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.FreightCharge,
Orders.OrderDate
HAVING (((Orders.OrderDate)>=[forms]![Report Date Range]![Beginning
Order
Date] And (Orders.OrderDate)<=[forms]![Report Date Range]![Ending Order
Date]));

In the Main Report Query, each record for Gross Order Total is showing
4
numbers to the right of the decimal.


----Main Report----
Record Source - Main Report Query
Filter on - No
Order By On - No
Sorting and Grouping - OrderDate - Ascending
Group Header - No
Group Footer - No
Group on Each Value
Group Interval - 1
Keep Together No

In Report Detail I have Gross Order Total and Sales Tax Total which
seem to
calculate fine...I think. The calculations are incorrect when I Sum
those 2
in the Report footer. This is where I am seeing it off by 1 or 2
pennies.
=Sum([Gross Order Total]) - Running Sum - No
=Sum([Sales Tax Total]) - Running Sum - No
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

percentage calculation...trying again 26
percentage calculation 2
Total Query 2
adding freight amount to total 14
Total is off by a couple of cents 4
...Ambiguous Field Reference..... 2
Crosstab subcolumn query/report heading pblm 3
NOT criteria in query not working 5

Top