Linking Same Tables (Current/Previous) in a Query / Sum is multiplying value results

I'm trying to link two tables in Access...both tables contain the samefields with YTD totals.  I need to subtract YTD Total between the two,to determine the difference.  I have the tables linked via 2 fields.I group by the two fields, and sum on YTD.  It is multiplying thevalues and I'm ending up with numbers in the 200,000 instead of max ofabout 5,000.I've tried doing queries separately, doing the sum, and then linkingthe two new queries...only to have the same results.I've tried turning them into Make-Table Queries, creating the tablesand running the new query off of the 2 tables where the values havealready been summed.  Same results.In one off moment...it worked correctly.  The next time I went intothe Query again though, the numbers were off again.  I cannot figureout what I did differently.What am I doing wrong?  How do I achieve the correct results where thesum value is not multipled?Thanks
0
Ben
3/22/2007 2:54:16 AM
access 16762 articles. 3 followers. Follow

1 Replies
981 Views

Similar Articles

[PageSpeed] 51

On 21 Mar 2007 19:54:16 -0700, "Ben" <bdicarlo1@yahoo.com> wrote:>What am I doing wrong?I don't know; what are you doing?Please post the SQL view of your queries. Sounds like your Joins arenonexistant or incorrect, but there's no way to tell from this distance!             John W. Vinson [MVP]
0
John
3/22/2007 3:35:11 AM
Reply:

Similar Artilces:

Problems saving a worksheet with Links
Does anyone know how I can resolve this issue ... I have a directory which contains 129 worksheets which have links to external data (in a Master Spreadsheet) -- I need to copy these files into a New Directory, but kee the Master Spreadsheet (which they are linked to) in the original location. If I do a simple Cut & Past, the Reference Link to the Master Spreadsheet gets moved to the New Directory (where the file does not exist), but if I open the worksheet (in the original directory/location) and Save As to the New Directory, the worksheet saved in the New Directory maintains its link t...

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

3-Color Scale Vlookup for Current Month/Previous/Pre-Previous
Is it possible to Conditionally Format a cell so that the colors are based off of VLOOKUP's? What I mean is I have a table on another tab and it has by month how complete the build is. I want the conditional format to color the percent of the current month red, percent of previous month yellow and pre-previous month green. I tried putting the formula =("41227-1",BuildData,18,FALSE) as the current, = ("41227-1",BuildData,17,FALSE) as the previous, and = ("41227-1",BuildData,16,FALSE) as the pre-previous. However, it uses only the cells value to colo...

data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...

links in emails
When I try to open a link included in an email nothing happens? Hi, This problem can be caused by several different things. Do you receive an error message when you click on a hyperlink, or does nothing happen at all? Can you use hyperlinks in other programs, such as Word? Please have a look at the following Knowledge Base articles to see if these resolve your problem. 177054.KB.EN-US OLEXP: Internet Shortcuts in Outlook Express Do Not Start Web Browser You can access this article by clicking on the link below. http://support.microsoft.com/default.aspx?scid=KB;EN-US;177054 257464.K...

how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...

Pivot Table Question #15
Hey all, Love pivot tables but i have a question, the table is set up as so Month Pallet# BoxesPur BoxesSold Price Frt Gross Net Jan 1 5 1.0 ..3 Formula Formula 2 5 2.0 .5 3 10 3 .15 Jan Total 10 10 6.0 ..95 So, the problem is in the Jan Total row, is there a way...

Timestamp value of GINA window presentation
I am currently troubleshooting slow logon times for a client with computer infrastructure spread across the globe. Before I start invesagating I want to create a baseline for logon times. I have modified the clients logon script to record when it starts and when it ends. I am using the computers tick count to record how long the computer has been on, but realize that there is a space of time between the user authenicating to the computer (control-alt-delete) and when the logon script starts, if the customer turns his computer on and then leave for an extended period of time I ...

Summing distinct rows in same cell
Sorry for the bad title I'm not sure hte best way to sum up this issue. Fruit Price Total Apples 0.69 40 Bananas 0.34 38 Lemons 0.55 15 Oranges 0.25 25 Apples 0.5 10 Pears 0.59 40 Almonds 2.8 10 Cashews 3.55 16 Peanuts 1.25 20 Walnuts 1.75 12 Apples 0.5 5 Given the data above I'm trying to sum the totals for only the Appl rows. So basically I need to search the Fruit column find the row that have "Apples" and then sum their corresponding Totals, giving m 55. I've been playing with this for hours with Lookups and Indexes bu am not really getting anywhere. Also, I can...

Tabulating Survey Results
I have just completed a survey in which the recepients answered a question with a 1-5 response. 1 being Not satisfied 5 being very satisfied - I have the totals for each response but want to be able to take the total responses in each category and create a average result of the question. Column one has the 1-5 in it - Column 2 has the number of reponses for each. I want the answer to be a 1-5 number off all the respondents. Thanks Something like this would work; =SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5) "Tabulatin Survey results" <Tabulatin Survey results@discussions.microsof...

IF / SUM
I have a spreadsheet with 17 rows, each representing a project name. In the columns, I have 2 cells for each week: Est and Actual. At the end of each row, I would like a row total for Est and another for Actual. As there are 20+ weeks represented, I cannot select the cells that have the criteria I'm looking for without using a more complex formula than SUM. Any ideas? The spreadsheet looks like this: Project Name Apr 3-9 Apr 10-16 Apr 17-23 Total Est | Actual Est | Actual Est | Actual E | A Project A 20 | 22 40 | 37 25 | 26 85...

Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...

form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...

Conditional sum
Hi Can you help? I would like to sum a matrix according to a condition in a row and in different condition in a column. I know how to do this one dimensionally (either in the row or the column) using sumproduct, but what about two dimensions? Let me give an example, number of widgets produced each week by each type of machine A B C D 1 weeks>>1 2 3 2 Type 1 10 15 11 3 Type 2 20 5 10 4 Type 1 5 12 21 Etc So, I’d like the formula to be a...

Excel formulation to automate values
Hi there, I have a little problem on arranging a small multi-worksheet excel here. Let me explain in a short way then give some details on it. I am trying to make an offers recordsheet which has two worksheets in it. One for products(and their base prices) and an offer sheet. I would like to use a bit dynamic data here. So when I type the name of the product excel would give me the price from other worksheet. in details; Prices worksheet is something like: A B 1 Product BasePrice 2 mouse 5 3 keyboard 8 4 ... And the ...

Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...

How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...

Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...

Forwarding balance for running sum
I created this query with a running sum, which is sorted by date then by transaction number. I put this into a form for entering transactions for an investment money market account. It works very well. However, now that I have one years worth of data in there, I would like to limit the data for the form as well as for reports. As soon as I limit the data range, I loose the beginning balance. Can anybody give me an idea on how I could create a beginning balance when I select a date range or a starting date? Anne wrote: >I created this query with a running sum, which is sor...

How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...

Mastering Great Plains Tables
Greetings, Does anyone have or know where I can acquire a detailed document detailing every single Great Plains table? I've used the data dictionary on and off over the years and have familiarity with a decent number of the important tables that are critical to my work, but is there something a little more detailed so that I can get at least a cursory understanding of *all* the tables? I'm thinking of creating a flash card for each table and on the back writing a brief sysnopsis of what it's used for, then regularly quizing myself. Failing anything else, I'll probably use th...

Email links open to My Documents
When I go to my blue hyperlinks in Outlook 2003, instead of being taken to the website, I am taken to my own My Documents....when I right click, I cannot copy and paste because the copy is grayed out. I really need to repair this ASAP and would appreciate any help. I am using Office 2003 and Windows XP Pro. Thank you! Try rebooting your system >-----Original Message----- >When I go to my blue hyperlinks in Outlook 2003, instead of being taken to >the website, I am taken to my own My Documents....when I right click, I >cannot copy and paste because the copy is grayed out. &...

Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...

Change the default format of the query design view
When I use the query design view, I have to increase the size of the table window (from which I'm selecting fields) sideways and vertically to see the field names more clearly and that also means moving the criteria grid further down the page to make room. Is there a way to change the default table window size and default grid position so that I dont' have to do this every time? Thanks, Pat Pat When you find it, let the newsgroup know! You are (unfortunately) not the first person to wish there was a setting...<g> Regards Jeff Boyce Microsoft Office/Access MVP "...

Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...