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
987 Views

Similar Articles

[PageSpeed] 12

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:

Sum formula is not adding up properly
I am summing up hours in Excel and the sum formula is not working Properly. For 2 of my 5 cells are adding correctly, but the other 2 when added to the formula throw the entire thing off. They are all formatted the same in 13:33 format to measure the # of hours spent on an activity. What would the reason be that two of them are not working? (it is almost like exel is substracting hours when these 2 cells are added) No real problem. Your math is probably fine; formatting needs fixing. Select the cell with the sum in it. Format this cell as Time 37:30:55 -- Gary's Student &quo...

Dynamics GP routing link several BOM's at once
When using routing links allow selection of several items at once to link to a routing step instead of one at a time. ---------------- 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 "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=487897...

How do I convert .txt files into .xls in order to be able to sum.
I receive via e-mail a statement that I need to resort by date , purchase order and total by department, etc. The process I have been using is to copy and paste into Excel. However, when I try to sum the $ is will not sum. Is there some other way of converting this statement that I receive via e-mail into Excel, so that I can resort and sum? I am using Excel 2002 and my e-mail browser is Mozilla. You might be able to use File-->Open, and then let the Text Import Wizard help. Otherwise, you could use the "data isn't recognized" fix here: http://www.officearticles.co...

Update Link
I have got a problem of Excel update link after upgrade to Office 2003. I have create 5 excel files which is linked togather. In the 1st one, it contains raw data, and each of the other make use of the result of the pervious file. 1 --> 2 --> 3 --> 4 --> 5 Data When I open the 5th file, it ask me to update link, form the background I can still see the values, however no matter I choose "update" or "do not update" the value turns into error. I have to open the 4th and 3rd file in order to give my value back. This does not happen when I open the fil...

Linked Forms
Hello, I am doing a project that requires two forms.. The first form contains the data for a business the second form contains data for the business owner... How do I link the two forms together...? Many thanks. Bob Send a common key piece of data from the first form (say the company name) to the second form page and include it in the second form as a hidden form field (then if using a database to store the results link with a relationship the 2 results tables by the common field) For form passing information see http://irt.org/articles/js063/index.htm -- ____...

remember path when linking many GIFs to cells?
When I link a number of GIF images residing in a different directory to Excel 2002 cells, the program does not remember the last directory it looked in when a new link is entered. Is there a way to set up a default path for this boring operation? Thanks, z.entropic ...

Link Tables in Report Writer
Hi everyone, Im working with Report Writer in GP10 I read David articles and they are interesting but my question is: for exemple I want to add the "expiration date" to the "POP Receivings Posting journal" report let's assume I found the table containing the "expiration date" how do I get to it when creating table relationships Im new with GP and Im having difficulties what tables to link to get the final table. I really tried everything to figure it out but I didn't succeed I hope I was clear explaning this. thank you guys in advance It sounds li...

format changes on pivot table although preserve formatting is che.
Post the question the body of the message, subject lines get truncated -- Regards, Peo Sjoblom "Aannd" <Aannd@discussions.microsoft.com> wrote in message news:282F34F1-42CA-4F0A-8A62-A71C64E30741@microsoft.com... > ...

Linked Cell Property In Activex controls
Can someone point me to an example showing how this property can be used, linking, as an example, an option button to a specific cell? Say if I wanted "1" to appear in cell B2 of the worksheet if the option button is clicked KG, The option button puts TRUE or FALSE into its Linked Cell. You can get 1 or 0 out of that by referring to it with double negation operators. = --A1 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KG" <KG@discussions.microsoft.com> wrote in message news:924E6B40-6040-4016-B75B...

Max Query
Hi all, I have 2 tables. One holds account numbers. One holds invoices, linked to account numbers. Ive got a query to show the latest invoice for each account number, via the MAX function. However, once this has been done, due to it being an aggregate function. There is no way of me editing this query once done. Is there any way around this, as i would like to only show the latest invoice, and edit information in that. Regards, A correlated sub-query in the WHERE clause might work for you if you don't have a lot of records. SELECT * FROM Accounts INNER JOIN Invoic...

Calling ReadFile after DeviceIOControl (Unexpected Results)
Hi folks, I'm using "LPT1" via CreateFile for Overlapped communications. I use WaitSingleObject and then GetOverlapped Result to collect ReadFile results. All that code, including overlapped WriteFile's work great and have been for years. However, now I need to get the BUSY state of the port. It appears the only way to do this in the context of Win32 is via: DeviceIOControl(Handle, IOCTL_PAR_QUERY_INFORMATION, Nil, 0, @status, SizeOf(status), @OVERLAPPED); This call works great, repeated adnausem and yields the proper results every time, in realtime....

Help with complex query 05-09-07
Hi all, I've got a query that I'm not sure how to develop. My tables: Quotes - QuoteNo, RaisedBy, Customer QuoteItems - RecordID, QuoteNo, PartNo, Lifecycle, Value There's a one-to-many relationship between Quotes and QuoteItems, i.e. one quote can have many items. I need to run a query to show a list of quotes with totals from the QuoteItems table i.e. QuoteNo, RaisedBy, Customer, List of PartNos, List of Lifecycles, TotalValue I haven't got a clue how to start this, I know it needs to be nested queries, but the listing of parts and lifecycles is particularly stumpin...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

Can I make a table flow like a text box story?
Does anyone know of a way to allow a table to act similar text block story. I would like to snake a long data table into two columns over several pages. Any ideas would be appreciated. Thanks, This is not at all possible. A Table will not move across pages. -- Thanks -- It would be a great feature -- but I couldn't find a way to do it either. BTW -- neither PM or Quark allow this either. >-----Original Message----- >This is not at all possible. > >A Table will not move across pages. > >-- > > >. > You can create a work around for this problem by...

MS Access Table missing
Guys, I'm looking at a database (Access2003) someone sent me and I see a Query, something like SELECT * FROM [Table 1] The problem is that there is no [Table 1] in table list, query list. In fact, there is no such object with the structure that I see when I open that original Query a=in design mode. The query opens and shows data.... Any thoughts? Few more observations: looks like this DB was converted before with errors; thiis DB uses linked tables Thanks! Dima. Assuming that this isn't a split database (do any forms show up?) with the tables in a separate...

Adding Index to RMSHeadquarters tables
I have a custom SQL script that reads the PurchaseOrder and PurchaseOrderEntry tables from the RMS Headquarters database. It does a join using StoreID and PurchaseOrderID. However, it does not appear that there is an index on these fields in Headquarters, so my query takes a long time. I would like to add indexes to these tables, which should speed up my query. What are the potential pitfalls of adding a custom index to an RMS table? Will the Reindex in Headquarters Administrator include that custom index when it does it's thing? -- Bill Yater Blue Horseshoe Solutions ...

Suppres Zero or empty Cell value in a line graph
Hi I'm using Office 2007. I have two charts using data from the same sheet. The second graph is a copy of the first. In the first graph, the empty and zero value cells are not displayed. In the second graph, the zero value cells is displayed (draged to zero) although the option "connect points with line" is checked. The strange thing: if I change the Y-data to another part of the sheet, it is no longer dragged to zero. Even if the cell is empty it's still dragged to zero A formula that returns "" is not an empty cell, it's a formula (or it's a small ...

MOP/SOP LINK #2
Hi, I created a new item and its fulfillment method is Make to Order-Manual. I created a sales order and a mfg order for this item. In GP 8.0, I opened the MOP/SOP Link window, only saw the MO on the right side for this item and the SO didn't show up on the left side. Did I miss any step to link MOP and SOP? What should I do, so the SO will show up in the left side? Thanks. stien Make sure the end date is set correctly, it must be out farther than the requested ship date on the SO (the default is the current date) also make sure you have a SITE ID in, even if you only have one s...

Creating a table into another Access database
Is there a VBA command or SQL statement for me to run a Make Table Query to create a table in another Access database? I would like to distribute several access databases to different groups with tables populated with data specific to the group. I don't want to create the tables in the main database and transfer the table, because there's many tables and it would probalby exceed the 2gb limit of MS Access. SELECT * FROM INTO [;Database=C:\Folder\File.mdb].Table1 FROM MyTable -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "anon" ...

Change column color in chart when column value is over/under goal
No use of VBA or macros expected. It is believed to be Excel chart feature. Any ideas on how to change column colors (Red/Green) if value exceeds or meets the preset goal. Eg. if goal is 4% - anything at or above 4 should show green and under 4 should be red. Assuming your data is in A2:A20 B2: =IF(A2>=4,A2,NA()) C2: =IF(A2<4,A2,NA()) copy B2:C2 down, add some labels to B1:C1, then chart B1:C20. This will give you two series, one for the aboves, one for the rest. Select each data series, right click, choose format, and set the colour as required. -- --- HTH Bob (there's ...

Graph OLE Linked objects problem
I got error message from access2003 when I tried to open a Graph OLE Linked objects, the message is: Strategic Budget builder can't find the file containing the linked OLE object you tried to update using OLE/DDE links command. When I click "yes", the other message pop up: "You may have misspelled the file name, or the file may have been deleted or renamed. If the file has been moved to a different location, use the OLE/DDE Links command to change the source. Or delete the object, and create a new linked object." Tried to re-installed OFFICE2003, but it still do...

Getting the "name=" (bit, picklist) value that is returned from SOAP
Hi all, I am getting several fields via a Web service request in javascript in an OnChange event to poulate other field on a case. One of those fields is a bit, and one is a picklist. Those nodes in the response come back with <attribute="new_active" name="true">1</attribute> for the bit field and <attribute="status" name="On Hold">3</attribute> How can I read the name value? I've tried selectSingleNode("//status").name with no luck. Thanks! I am doing a similar process, using a SOAP response message. I ge...

which table Picklist integer value stored?
Hi there, Which database table pick list interger values stored? thanks Kyaw this query will list all the pick list integervalues and attribute name select AttributeName,AttributeValue,Value from StringMap // for account WHERE (StringMap.ObjectTypeCode=1) //for contact WHERE (StringMap.ObjectTypeCode=2) attributename will tell u about the name of the picklist attribute value is the intigers and values are what u can see on the form i think this will help u ...

Pre-configuring Pivot table "views"
I need to create several different pivot tables from the same set of data. However, I need to keep switching between the different tables and it quickly becomes a cumbersome process selecting different rows, columns, and sigma values, each time I want to look at a certain pivot table. I know I can create different pivot tables and put them in seperate sheets instead of dynamically updating the same pivot table to get the data I want. I was wondering if there was a way I could preconfigure all these tables and simply "select" one of the "views" so I wouldn't ...

Data format in pivot table
I am running a Pivot table on some swim data. Even though the data is formatted the same way "mm:ss.00", the fraction of the second is not showing up or is not part of the numbers in the Pivot table. Pivot table data Back 25 Breast 25 Fly 25 00:31.00 00:27.00 00:28.00 00:31.00 00:33.00 00:31.00 00:36.00 00:31.00 00:27.00 00:28.00 00:23.00 00:25.00 00:24.00 Data the Pivot table is based on 7 CMSA-SE 00:21.87 00:21.49 6 BMAC-SE 00:22.95 00:21.91 7 BMAC-SE 00:23.13 00:22.16 6 BMAC-SE 00:27.97 00:22.63 8 BMAC-SE 00:21.07 00:22.70 7 UN-SE 00:00.00 00:22.94 6 CMSA-SE 00:26.36 00...