If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dealing with Pecentage Calculations in Reports
Can anyone help please? Report Gross Grand Total and Sales tax Total are off
by a penny or 2 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 ----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 |
Thread Tools | |
Display Modes | |
|
|