Total is off by a couple of cents

I have a report that feeds of a query.  The query has the following fields
matsales: [order details]!unitprice*[order details]!quantity
scharge: [order details]!surcharge*[order details]!quantity
Taxes: ([matsales]+[scharge])*0.0875

0.0875 is  that tax values

On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are 
correct however, =Sum(Taxes) is off.  For example the total should be $161.88 
after the taxes has been rounded off in two decimal places but its giving me 
$161.85 the actual total if the taxes were not rounded off.

The matsales and scharge total are correct and they are rounded as well.  I 
think the problem im having is similar in excel if its not "Set as precision 
as displayed."  
I have tried every possible format in both the sum field in the report and 
query, I even converted the numbers to CSng or Cdbl.  Tried rounding off the 
quesry as well, and none worked.

Is there a way on access to sum the values as displayed if not is there a 
solution for the problem I described above.

Thanks
0
Utf
5/25/2010 8:50:01 PM
access.reports 4434 articles. 0 followers. Follow

4 Replies
728 Views

Similar Articles

[PageSpeed] 13

The problem is that Taxes needs to be rounded off during the calculation.

Taxes: Round(([matsales]+[scharge])*0.0875,2)

AND when you sum
SUM(Round(([matsales]+[scharge])*0.0875,2))

Otherwise small discrepancies are going to creep into your calculation of the 
Total Tax.  You may see 0.39 for the calculation [matsales]+[scharge])*0.0875 
but the actual result may be 0.39375.  When you total a few like that before 
rounding you end up with extra pennies.  On the other hand .3965 will display 
as .40 and total a few of those and you might loose a penny or two.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

FCP wrote:
> I have a report that feeds of a query.  The query has the following fields
> matsales: [order details]!unitprice*[order details]!quantity
> scharge: [order details]!surcharge*[order details]!quantity
> Taxes: ([matsales]+[scharge])*0.0875
> 
> 0.0875 is  that tax values
> 
> On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are 
> correct however, =Sum(Taxes) is off.  For example the total should be $161.88 
> after the taxes has been rounded off in two decimal places but its giving me 
> $161.85 the actual total if the taxes were not rounded off.
> 
> The matsales and scharge total are correct and they are rounded as well.  I 
> think the problem im having is similar in excel if its not "Set as precision 
> as displayed."  
> I have tried every possible format in both the sum field in the report and 
> query, I even converted the numbers to CSng or Cdbl.  Tried rounding off the 
> quesry as well, and none worked.
> 
> Is there a way on access to sum the values as displayed if not is there a 
> solution for the problem I described above.
> 
> Thanks
0
John
5/26/2010 1:01:54 PM
John,

I appreciate the respond and it works however, I have one more question if 
we have a number/currency like 10.325 and we round it to two decimal place as 
you suggested would this number be 10.32 or 10.33.  Access is giving me 10.32 
I thought in math anything 5 or above should be rounded to the next number, 
does Access think the same way.  I do see 10.33 if I round the field in the 
report into two decimal place under the field properties by not using the 
formula of Round([exp],2).

Thanks again

"John Spencer" wrote:

> The problem is that Taxes needs to be rounded off during the calculation.
> 
> Taxes: Round(([matsales]+[scharge])*0.0875,2)
> 
> AND when you sum
> SUM(Round(([matsales]+[scharge])*0.0875,2))
> 
> Otherwise small discrepancies are going to creep into your calculation of the 
> Total Tax.  You may see 0.39 for the calculation [matsales]+[scharge])*0.0875 
> but the actual result may be 0.39375.  When you total a few like that before 
> rounding you end up with extra pennies.  On the other hand .3965 will display 
> as .40 and total a few of those and you might loose a penny or two.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> FCP wrote:
> > I have a report that feeds of a query.  The query has the following fields
> > matsales: [order details]!unitprice*[order details]!quantity
> > scharge: [order details]!surcharge*[order details]!quantity
> > Taxes: ([matsales]+[scharge])*0.0875
> > 
> > 0.0875 is  that tax values
> > 
> > On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are 
> > correct however, =Sum(Taxes) is off.  For example the total should be $161.88 
> > after the taxes has been rounded off in two decimal places but its giving me 
> > $161.85 the actual total if the taxes were not rounded off.
> > 
> > The matsales and scharge total are correct and they are rounded as well.  I 
> > think the problem im having is similar in excel if its not "Set as precision 
> > as displayed."  
> > I have tried every possible format in both the sum field in the report and 
> > query, I even converted the numbers to CSng or Cdbl.  Tried rounding off the 
> > quesry as well, and none worked.
> > 
> > Is there a way on access to sum the values as displayed if not is there a 
> > solution for the problem I described above.
> > 
> > Thanks
> .
> 
0
Utf
6/1/2010 4:13:01 PM
Round uses Banker's Rounding.  That means if the last digit is  5 the rounding 
takes place toward the nearest even number so .235 rounds to .24 and .245 
rounds to .24.  The theory is that this will be closer to the correct amount 
if you round a lot of numbers.

There are other rounding algorithms that will round the way you wish.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

FCP wrote:
> John,
> 
> I appreciate the respond and it works however, I have one more question if 
> we have a number/currency like 10.325 and we round it to two decimal place as 
> you suggested would this number be 10.32 or 10.33.  Access is giving me 10.32 
> I thought in math anything 5 or above should be rounded to the next number, 
> does Access think the same way.  I do see 10.33 if I round the field in the 
> report into two decimal place under the field properties by not using the 
> formula of Round([exp],2).
> 
> Thanks again
> 
> "John Spencer" wrote:
> 
>> The problem is that Taxes needs to be rounded off during the calculation.
>>
>> Taxes: Round(([matsales]+[scharge])*0.0875,2)
>>
>> AND when you sum
>> SUM(Round(([matsales]+[scharge])*0.0875,2))
>>
>> Otherwise small discrepancies are going to creep into your calculation of the 
>> Total Tax.  You may see 0.39 for the calculation [matsales]+[scharge])*0.0875 
>> but the actual result may be 0.39375.  When you total a few like that before 
>> rounding you end up with extra pennies.  On the other hand .3965 will display 
>> as .40 and total a few of those and you might loose a penny or two.
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> FCP wrote:
>>> I have a report that feeds of a query.  The query has the following fields
>>> matsales: [order details]!unitprice*[order details]!quantity
>>> scharge: [order details]!surcharge*[order details]!quantity
>>> Taxes: ([matsales]+[scharge])*0.0875
>>>
>>> 0.0875 is  that tax values
>>>
>>> On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are 
>>> correct however, =Sum(Taxes) is off.  For example the total should be $161.88 
>>> after the taxes has been rounded off in two decimal places but its giving me 
>>> $161.85 the actual total if the taxes were not rounded off.
>>>
>>> The matsales and scharge total are correct and they are rounded as well.  I 
>>> think the problem im having is similar in excel if its not "Set as precision 
>>> as displayed."  
>>> I have tried every possible format in both the sum field in the report and 
>>> query, I even converted the numbers to CSng or Cdbl.  Tried rounding off the 
>>> quesry as well, and none worked.
>>>
>>> Is there a way on access to sum the values as displayed if not is there a 
>>> solution for the problem I described above.
>>>
>>> Thanks
>> .
>>
0
John
6/1/2010 6:32:52 PM
Hi John,

Again thank you for responding, the other rounding algorithm you mentioned 
where can I find those formulas?  Also would the sum in the report be 
accurate or precise  if I round the values using those algorithms?  For 
example

The Report values using the Round([xpr],2)
           3.74
          10.32 (this can be rounded to 10.33, however the total will still 
be 17.80 in the Sum Report, which was my original problem)
           3.74
Total:  17.80

We would prefer that 10.325 becomes 10.33 and the total Sum in the report 
equals to 17.81.  Can this be possible?

"John Spencer" wrote:

> Round uses Banker's Rounding.  That means if the last digit is  5 the rounding 
> takes place toward the nearest even number so .235 rounds to .24 and .245 
> rounds to .24.  The theory is that this will be closer to the correct amount 
> if you round a lot of numbers.
> 
> There are other rounding algorithms that will round the way you wish.
> 
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
> 
> FCP wrote:
> > John,
> > 
> > I appreciate the respond and it works however, I have one more question if 
> > we have a number/currency like 10.325 and we round it to two decimal place as 
> > you suggested would this number be 10.32 or 10.33.  Access is giving me 10.32 
> > I thought in math anything 5 or above should be rounded to the next number, 
> > does Access think the same way.  I do see 10.33 if I round the field in the 
> > report into two decimal place under the field properties by not using the 
> > formula of Round([exp],2).
> > 
> > Thanks again
> > 
> > "John Spencer" wrote:
> > 
> >> The problem is that Taxes needs to be rounded off during the calculation.
> >>
> >> Taxes: Round(([matsales]+[scharge])*0.0875,2)
> >>
> >> AND when you sum
> >> SUM(Round(([matsales]+[scharge])*0.0875,2))
> >>
> >> Otherwise small discrepancies are going to creep into your calculation of the 
> >> Total Tax.  You may see 0.39 for the calculation [matsales]+[scharge])*0.0875 
> >> but the actual result may be 0.39375.  When you total a few like that before 
> >> rounding you end up with extra pennies.  On the other hand .3965 will display 
> >> as .40 and total a few of those and you might loose a penny or two.
> >>
> >> John Spencer
> >> Access MVP 2002-2005, 2007-2010
> >> The Hilltop Institute
> >> University of Maryland Baltimore County
> >>
> >> FCP wrote:
> >>> I have a report that feeds of a query.  The query has the following fields
> >>> matsales: [order details]!unitprice*[order details]!quantity
> >>> scharge: [order details]!surcharge*[order details]!quantity
> >>> Taxes: ([matsales]+[scharge])*0.0875
> >>>
> >>> 0.0875 is  that tax values
> >>>
> >>> On the report the sum in the footer =Sum(matsales) and = Sum(scharge) are 
> >>> correct however, =Sum(Taxes) is off.  For example the total should be $161.88 
> >>> after the taxes has been rounded off in two decimal places but its giving me 
> >>> $161.85 the actual total if the taxes were not rounded off.
> >>>
> >>> The matsales and scharge total are correct and they are rounded as well.  I 
> >>> think the problem im having is similar in excel if its not "Set as precision 
> >>> as displayed."  
> >>> I have tried every possible format in both the sum field in the report and 
> >>> query, I even converted the numbers to CSng or Cdbl.  Tried rounding off the 
> >>> quesry as well, and none worked.
> >>>
> >>> Is there a way on access to sum the values as displayed if not is there a 
> >>> solution for the problem I described above.
> >>>
> >>> Thanks
> >> .
> >>
> .
> 
0
Utf
6/4/2010 4:02:02 PM
Reply:

Similar Artilces:

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...

Calculate Total Books
I am preparing packing slips for boxes to be sent out. There are different book orders going into the same box. Each book in the order will have its own set of id numbers that are usually, but not always, in a string(101-121 etc). I use a calculated field on the form to display the number of books in the string as I store the start book number and subract it from the end book number and add 1 to get the actual number of books in the string. I use the same calculated field on the report to show the number of books in each detial, but I have multiple strings in 1 box. I need to show a...

total for bills
Is it possible to have the total of the bills not yet paid in the current or for the upcoming month? Thank you in advance. Bepp ...

Totaling Hours Worked
I have one column that has my start time (6:30 AM) and one column that has my end time (3:30 PM). How do I make the third column total the hours I worked (9.00)? Thanks for any help. Debra Ann (EndTime - StartTime)* 24 To Excel, a day = 1 and an hour = 1/24 so, to convert an hour value "up" to an integer, multiply by 24. Hope this helps, -- George Nicholson Remove 'Junk' from return address. "Debra Ann" <anonymous@discussions.microsoft.com> wrote in message news:022801c3d6d5$39f8a340$a101280a@phx.gbl... > I have one column that has my start time ...

Total
Any idea why this won't work? TotalDays : nz([Sum Of VACADAYS])+nz([Sum Of SICKDAYS])+nz([Sum Of OUTNOPAY])+nz([Sum Of HOLIDAYS])+nz([Sum Of BEREAVEMENT]) I get an error that say: SubQueries cannot be used in the expression. Any help would be appreciated! JK JK - Are the field names correct? For example, is [Sum Of VACADAYS] a field name in the source table or query? If you are basing this query on a query that sums the records, Access usually names the fields [SumOfVACADAYS]. If this doesn't help, post your SQL (go to SQL View and copy/paste the code into...

How do I sort a Piviot table by the Total field in Excel 2002
I need help with how to sort Piviot table not in alph order. and using a total. On Thu, 13 Sep 2007, in microsoft.public.excel.charting, "dawn.pitzer@poloralphlauren.com" <dawn.pitzer@poloralphlauren.com.invalid> said: >I need help with how to sort Piviot table not in alph order. and using a >total. Use Right click.. Field.. Advanced.. Autosort Options.. Ascending.. by Total -- Del Cotter NB Personal replies to this post will send email to del@branta.demon.co.uk, which goes to a spam folder-- please send your email to del3 instead. ...

How do I get a total
A1 through A9 Has part numbers B1 through B9 has price C1 through c9 has quanitys required to make a set D1 gives total Price of set A1 part#1 B1 $65.00 c1 requires 3 for a set D1 total price -- Thanks Mike Mike, not quite sure what you want but here goes anyway. Assuming that you have:- price of 65 in cell B2 quantity of 3 in cell C2 Put:- =(B2*C2) - in cell D2 and this will give you an answer of 195 (in cell D 2). If this is what you want please hit Yes. Thanks. "hotrodflatglass" wrote: A1 through A9 Has part numbers B1 t...

Total paid in Commissions
On my Home Page I am informed, under investment performance, of the total I have paid in commissions in the current month. Is it possible to find out the total paid in commissions over a period, say 3 months, or a year? Any advice would be gratefully accepted! ...

Totaling Invoices by Date
Can someone tell me how to make Excel 2000 add up all the invoices on a certain date automatically? For example- (in this order) 6/1/04 $34.45 6/1/04 $23.87 6/5/04 $12.56 6/3/04 $10.00 6/1/04 $100.00 Would return something like this- 6/1/04 $158.32 6/2/04 $0 6/3/04 $10 6/4/04 $0 6/5/04 $12.56 Currently, I am totaling it up myself which can get to be kind of a pain. I know it can be done but I am not sure if it can be done with Excel 2000 or not. Is it a macro? Thanks a billion. Hi, Assuming you use your data from your first table, starting at A1...

Can you Roll-Up Work Totals by Resource Group?
'ello newby apologies if this has been answered before Is there a way to have separate roll-up totals for different resource groups? Scenario - I have 2 teams working on a project, my team and a group from my Client. Ideally i'd like to have my gantt chart show 3 columns: 1. total Work 2. Work by my Team and 3. Work by the Client Team. Doable? TIA! Mikey, The Gantt Chart is a view of tasks; whereas you are asking for a view of resources. They are different. Try this: Menu: View/Resource Usage. Then Menu: Project/Group by ..., pick Resource Group (whi...

How do I subtract 20% from one column in Excel and place totals i.
I would like to know if someone knows the formula I use to subtract 20% from totals in one column on my spreadsheet to show totals in another column on same spreadsheet. Can anyone help? Spent way to long trying to figure out correct formula. =A30*80% assuming totals in A30 -- HTH RP (remove nothere from the email address if mailing direct) "Full Effect Landscaping" <Full Effect Landscaping@discussions.microsoft.com> wrote in message news:7ACF893B-54DF-4EE4-A931-3BB5FA365821@microsoft.com... > I would like to know if someone knows the formula I use to subtract 2...

Display total weight on POS Screen
Is anyone aware of a way to display the total weight of an sale or order to the cashier during POS? Hi Bill, Yes it is possible, you have to use the specific item type called weighted, and for that you also require to create the itemlookupcode which has start and end digit of weight item. Here is how weighted barcode works: following steps one by one. ASSUME: 2010190013636 is the Barcode Content. 1) Try creating a Weighed Item, by giving Item Look Up code = 10190 and Alias Code = BLANK [No alias code], set the unit price and test by scanning that particular barcode in PO Screen...

$ totals not correct
The investment data (price and # of shares)is correct, but the totals are not. Is there a fix? ...

Split Transactions do not total transaction amount (See Warnings)
I have a bunch of transactions where the total of the items split into different categories does not add up to the transaction amount. I've noticed that for electronically received transactions, I get a message on the transaction that says "See Warnings", but I don't seem to see this for transactions that have been accepted or were entered incorrectly (not electronically received). Is there any way to write a report to find these transactions where the different categories do not add up to the transaction amount? This is a great question. I'm not thinking of a way to g...

No Pivot Table Totals Wanted
Is it possible to get pivot to not put the totals in for the Row are besides going to each field and saying none under the "Field Settings" I put these pivot tables together, and everytime I have to go back an change the field settings so I get no totals. I don't want the totals I thought through the table options by taking the checks out of th grand totals and auto format it would take care of it but it doesn't Just getting tired of changing those settings individually each time Any ideas is there a way I hope someone has something or maybe I a going brain dead let me know ...

show in a excel graphic a total percentage for 5 diff data fiels
I need to make a graphic showing the percentage for 5 diff fields, e.i. pmc total 16, nmcs total 10, pmcs total 5, nmcm total 0, and nmcs total 3, this it should be based on a total of 100% combine. at this tiem I can't figure out the formula how to make read all 5 columns, please help. If need it I can forward the project file to make better sense of it. Thank you. Julio Select the 5 fields and create a pie chart. The size of each slice depends on the % value of that data item compared to the total. I don't understand the 2nd bit about "formula to make read all 5 column...