Sum Calculation Incorrect

Hi,

I've got a problem that I don't even know where to start for fixing it. I
have a Query, qryPortfolio which has a field MVbase. Another field Type can
be 'Equities' 'Cash' or 'Options'. If I filter on Options and group
everything to just get one figure for the sum of MVbase, it comes up with a
figure that is incorrect, as in, if I export the data to excel, excel comes
up with the correct sum but access won't.

It seems it's leaving out certain numbers (though I can't narrow it to exact
records...) all the numbers involved are doubles so I can't work out why this
is happening...any ideas what could possibly cause this to calculate
incorrectly? Does the same incorrect calculation as Sum on reports too....

Thanks.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200706/1

0
Jackson
6/20/2007 11:08:29 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1674 Views

Similar Articles

[PageSpeed] 47

HI Dale,

Thanks for your reply. The figures are off by around 1.3 million (similar
amounts over two days of data) so not a rounding issue. The other ones work
fine for Type and calculate correctly, I know excel is correct becaused added
them up manually too. If I display the records in the query or report and
then add them up manually I get what excel gets, it's literally only
malfunctioning when aggregating into a sum...its really strange...

Dale Fye wrote:
>How much off are the two calculations?  How do you know that Excel is correct 
>and not Access.
>
>Many double precision numbers wil contain round-off errors.  Do you have the 
>same computational problem when you filter on one of the other "Type" values?
>
>Dale
>
>> Hi,
>> 
>[quoted text clipped - 11 lines]
>> 
>> Thanks.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200706/1

0
Jackson
6/20/2007 12:58:14 PM
Reply:

Similar Artilces:

P.O. Generator calculation based on sales
Purchasing certain items depends not only on 'order point' or 'order up to level', but on a certain season of the year (Thanksgiving, X-mas, etc). Create an option to calculate quantities based on sales in a certain period from a previous year. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click ...

Calculating using IF and AND to show numbers based on %
Hi, I have an issues which should be solved in Excel, but I cannot seem to find an answer, here's the problem. In cel H2 is a percentage of target realisation. In cel I2 is the formula which will give a numer of points based on the percentage in H2. E.G: if H2 has the percentage 100% then cel I2 should show the number "30" if H2 has the percentage 100,07% then cell I2 should show the number "30" if H2 has the percentage 102% then cell I2 should show the number "40" This is the table on which the point are given: 0-100% = 0 (points) 100 till 101 = 30 (poin...

Speeding up Excel calculations
Hi All, I have a workbook with a lot of array functions. To give an idea: - 17 sheets of which 15 with about 150 array functions per sheet - 1 sheet contains the raw data, > 10,000 rows - I have to use array functions because several conditions have to be checked It is all working fine, the only thing is that it takes 30 minutes or more to recalculate the workbook. So, you can imagine that I try to avoid recalculations as much as possible. Does one have some good tips to speeding up Excel? With kind regards, JP Smaller ranges to check?? If desired, send...

How do I calculate checked boxes in a report or query?
I have created a table and a form with yes/no checked boxes, now I'd like to make a report or a query and in this I'd like to calculate how much was done (according to a checked box) from a respective year. How can I do this? Use date criteria and this for the count --- Sum(Abs(Nz([YourCheckBox],0))) -- KARL DEWEY Build a little - Test a little "leona" wrote: > I have created a table and a form with yes/no checked boxes, now I'd like to > make a report or a query and in this I'd like to calculate how much was done > (according to a checked box) fr...

Sum
Hi, I have a following data in a column on my spreadsheet 0.00 -98.35 #N/A -222.89 56.45 0.00 13.60 19.96 When trying to use the Sum function it doesn't work as it doesn' appear to like the #N/A. Any suggestion would be gratefully received. Thanks Simo -- sgrec ----------------------------------------------------------------------- sgrech's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1450 View this thread: http://www.excelforum.com/showthread.php?threadid=27181 sgrech wrote: > Hi, > > I have a following data in a column on my spreadshee...

Columm sums
Hi all Thanks for help in the past & future I have a columm of numbers to be added There are about 1200 of these running say from cells C1 down to C1200 I normally use the =SUM(C1:C1200) function but often I only want to total part of these - say from cells 250 to 650 I usually manually change the function each time - I am wondering if there is some function I could use where I could input say into cells B1 and B2 the numbers of the range of cells in the C columm I want to be totalled Many Thanks Alex Hi Alex, If cell A1 contains the row no to start from - say 6 and cell A2 has...

Is there a way to sum a column using criteria from multiple column
I'm not sure how to ask this, I am currently working on a spreadsheet that has an itemized worksheet depicting each budget approval (Sheet2). I have it tied into our complete budget so that it automatically adds and/or subtracts the amounts according to the date... =SUMIF('Sheet2'!$K:$K,"="&(TEXT(D$5,"mmm-yy")),'Sheet2'!$E:$E) ....but I also need it to be specific to the "BUD#" and "LINE#" (see below). SHEET2 A B C D E BUD # LINE # CAF Date Constructed Key Field Dollar Amount AUBUD 01 12/9/05 =TEXT(C1,"mmm-yy"...

Calculating tax Formula
I am constructing a payroll spreadsheet. In the cell that calculates the Federal TaxI have the following: =if(i7<100,(i7*.04769)=if(i7>101<150,(i7*.05693))). The value in i7 is 140 and it returns a FALSE in cell i7 instead of a dollar amount. Any help would be greaatly appreciated. Hi IFs are made like =If(Test, test true then this, Otherwise this) You missed a comma after "if true then this", and you don't have an"otherwise"after the second test.. Also; i7>101<150 is not valid syntax. HTH. Best wishes Harald "DWright" <DWright@discu...

How does Money Plus generate and calculate past trends for cash fl
I am relatively new to this software. I have noticed that at a certain point, Money Plus begins to project cash flow based on past trends. It seems to happen randomly, except that first you have to enter one or more transactions that are being trended. What formulas, algorithms, or other methods or calculations does Money use to generate these numbers? I haven't figure out when or why they appear, or what transactions or types of transactions trigger their appearance. I find them useful and I would like to know when I will get my "amounts based on past trends" for gas...

How do I store calculations from a form to a database table?
My boss and I are stuck trying to figure out how to make our calculations be stored in our database table. Currently our calculations are in the design view of the form. We are able to see the anwer to the calculations, but they are not stored in the database table. What are we doing incorrectly? Is there some other place or way that we are supposed to do calculations? Thank you for your help. The short answer is - don't. In most cases you should not try to store calculated values in a table. Calculations should be done in queries or in unbound controls on forms. Storing the value ...

date from query when brought into excel is incorrect
We are having an issue with Excel where a user who is using the web version of Business Objects is doing a query. They then save the query as an Excel document. The problem is that the date in excel is different than the query results. The dates that are 01/01/1900 in Business Objects show as 01/02/1900 in Excel. The only date that doesn't change is 1/1/2005. Is this a known issue? If it is, is there a fix for it? I found an article on the differences between 1900 and 1904 dates in excel but it didn't seem to address the same issue. We are using Office XP (2002). ...

Subtotal
OK, so I have a list of orders. Some orders have just one row of data, some have two rows of data, some have three, some have four, etc. Each row represents either a delay or an activity. (So, obviously, an order can have more than one of either.) I need to aggregate into one row: Order | Type of Order | Num. of Delays | Total Length of Delays HERE'S THE MANUAL WAY I'M DOING THIS NOW: ====================================== My initial columns are: Order Number (A) | Type of Order (B) | Action Code (C) | Action Length (D) I added a "marker column" - Dela...

How do I subtract a date from a date for a sum of total years?
I am trying to calculate how many years an employee has worked. I have start date and current date. What is the formula to return years worked? If the current date is in A1 and the start date is in B1, then =year(A1)-year(B1) formatted as general will return the number of years worked. -- Gary''s Student "Paige" wrote: > I am trying to calculate how many years an employee has worked. I have start > date and current date. What is the formula to return years worked? Hi Paige One way =DATEDIF(Startdate,currentdate,"y") for years only =DATEDIF(Start...

How to change the Subtotal of my Pivot table to use subtraction instead of Summing
I have the following Pivot table created: --------------------------------------------------------------------------- Sum of HEAD_COUNT DEPT PER_GROUP TYPE HQ HRD ED PD CFD --------- ---- -- --- -- -- --- ACCOUNTING 1APPROVED 0 0 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ACCOUNTING Total 0 0 0 0 0 0 ADMINISTRATIVE 1APPROVED 0 1 0 0 0 0 2ACTUAL 0 0 0 0 0 0 ADMINISTRATIVE Total 0 1 0 0 0 0 CORPORATE SUP 1APPROVED 1 0 37 1 8 7 2ACTUAL 1 0 35 1 8 7 CORPORATE SUP Total 2 0 72 2 16 14 ----------------------...

SUM Function not Updating
Hello, One of our business partners is having trouble w/an Excel document. They have the SUM function used multiple times throughout the document, and as they have changed values in certain fields, the SUM has automatically updated itself to reflect this. It's not working anymore. I have tried to redo the formulas but they simply won't work. The SUM stays the same regardless of the values in the cells. I can't even create a brand new column w/the SUM function at the bottom...it won't up date either. In a new document I can get the SUM function to work properly, how...

use Vlookup for sum all found value?
Hi, I am new here and have a problem hope I can get help in here! I have two worksheet "W1" and "W2" In W1 worksheet, it has a column "emp_pin" which is unique. This value is used in the Vlookup function to find a numeric value "Paid" in W2 worksheet. If "W1" and "W2" are one to one relationship, it will work fine. However I have encountered the one to many relationship, it means one row in "W1" has many rows in "W2". I just wonder if I can sum up all the found value "paid" in "W2". should...

Mortgage calculator
Has anybody used a mortgage or loan calculator from one of the banking sites? I would like to build one in Excel. Any help would be appreciated. Hi Aaron, There is a template here: http://search.officeupdate.microsoft.com/TemplateGallery/ct139.asp Pretty good discussion about how the formulas work here: http://makeashorterlink.com/?Y47922935 Also, in Norman Harker is out there, he has in the past offered to send a few examples, by direct request. tim "Aaron" <aaron1@nbnet.nb.ca> wrote in message news:92338c73.0307101055.6f7abccb@posting.google.com... > Has anybody us...

Omit zeros in calculation
Is there a way/formula to omit records with a zero value when calculating an average on a report? In the Report Footer section, add a text box. Set its Control Source property to something like this: =Avg( IIf([Amount] = 0, Null, [Amount]) ) Substitute your own field name for Amount. This solution uses the fact that Access ignores nulls when calculating averages. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Whitney" <Whitney@discussions.micros...

Need calculated control to work
I have a subform with fields: first name, last name, phone, expiration date, membership status. I want a calculated control to display the total number of records that appear in the subform. How do I do this? -- jj The simplest way to do this is to set the subform's NavigationButtons property to Yes. The left end of the horizontal scrollbar will then display something like: 4 of 199 records If you don't want to do that, life is not so simple. If the form is in Form view or Continuous View, you could add a text box to its Form Footer section, and set the Control Source to: ...

Making a calculation in report footer of every record
Hi All, I'm trying to to make a total in my report footer. I'm explaining; In Page Header: (I have the following labels) ID Hour Door TransportOrContainer Footwear ALDO Feet-First Transit Globo Handbags Aldop NonALDOp Accessories ALDOQ NonAldoQ In TransportOContainer Header: (I have the following text box which make my the records group by TransportOrContainer) TransportOrContainer Header In the Detail section: (I have the following text boxes) ID Hour Door TransportOrContainer Footwear ALDO Feet-First Transit Globo Handbags Aldop NonALDOp Accessories ALDOQ NonAldoQ And in th...

Calculate the number of days to exclude Sunday & Holidays
I need to know what the third business day from a Manually entered date in Cell B37 to exclude Sundays and Holidays. So far I have: =WORKDAY(B37,3,Holidays) But I realized that I need it include Sat. and this formula won't do that. Any suggestions? THANKS! -- -Liz Hi, Try this ARRAY formula. Holidays is a named range that you enter holiday dates in. This adds the number of days in B1 but if you want you can change every instance of B1 to a 3 (4 of them) =B37+SMALL(IF(WEEKDAY(A1+ROW(INDIRECT("1:"&B1*10)))<>1,IF(ISNA(MATCH(A1+ROW(INDIRECT(&q...

Help! Incorrectly addressed email
Hello all At the moment, the company is receiving email with a username@ourdomain.com which you would expect. The only problem is, the username is no one on our domain but the mail is still going to some peoples inboxes. Can someone please explain to me how/why this can happen and what I can do to stop it? We are running exchange server 2003. Many thanks, Is it being BCC'd to them? "Gareth Howe" <no@spam.com> wrote in message news:ek5jkQfUEHA.2356@TK2MSFTNGP10.phx.gbl... > Hello all > > At the moment, the company is receiving email with a username@ourdom...

Dataset (ds.writexml) written to xml file incorrectly
Hello all. I'm reading the data from an XML file similiar to below, using the code below. When the file is edited in the datagridview and then re-written, it doesn't write the xml file as it was originally written. So what am I doing wrong? My code and samples are below. Any help would be greatly appreciated!! VISUAL STUDIO 2005 ..NET 2.0 FRAMEWORK, VB.NET CODE Declare a bindings source, a dataset, a binding navigator, and set up a datagridview where the data is displayed & edited 'now associate them bndgSrc1.DataSource = ds dgvConfig.DataSource = bndgSrc1 binNav1.BindingS...

Global Calculation ???
Using Excel 97 I have a number of spreadsheets where I have to do a convert all amounts with a calculation. Is there a global way to do this. There's quite a few cells and spreadsheets ... would be nice to not have to tell each cell to do this. (even using the fill function would be onerous). Thanks a bunch for any short-cuts. Diana Hi Do you want to apply the same formula to these cells, or what? You can make use of a macro to achieve your goal, but the macro itself will be difficult to write without any guidelines? -- j.kasselman@atlantic.net.remove_2nd_at. Randburg, Gaute...

Sum of the Largest Values
Hi. I have a gradebook. I have 16 assignments, and thus have 16 columns for scores. However, I would like to include ONLY the highest 10 scores in the final grade. In other words, how can I find the sum of the largest 10 values in a 16-cell range? Thanks! =SUM(LARGE($A$1:$P$1,ROW(INDIRECT("1:10")))) arary entered using CTRL+SHIFT+ENTER -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Best wishes to all, and hope f...