Could Not Add The Table Error
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Syntax for subqueries in the FROM clause using multiple INNER JOIN's in MS Query up vote 1 down vote favorite I've been trying for a few days now (grrr) to pull data out of an ODBC-connected database using the manual SQL statement window in MS Query. I can see what it is I would like to do, but am getting hung up on the syntax (which I'm pretty sure is specific to MS Query). The problem is this: I have four tables: waybill, waybill_item, rail_bl, and rail_bl_item, which between them contain information on the weight processed through a shipping facility, with cargo either being routed onto trucks (waybill), or rail (rail). The waybill and rail_bl tables contain date data for truck and rail cargo, respectively; whereas the waybill_item and rail_bl_item contain data on the weight of those items. I want a table that will return the total weights processed -- both rail and trucking -- for each day. I.E. DATE | WEIGHT date1, (truck_weight_for_date1+rail_weight_for_date1) date2, (truck_weight_for_date2+rail_weight_for_date2) date3, (truck_weight_for_date3+rail_weight_for_date3) etc By performing the query, SELECT waybill.creation_date, sum(waybill_item.weight) FROM waybill INNER JOIN waybill_item ON waybill.id = waybill_item.waybill_id WHERE waybill.creation_date > '01/01/2012' GROUP BY waybill.creation_date UNION SELECT rail_bl.creation_date, sum(rail_bl_item.total_weight) FROM rail_bl INNER JOIN rail_bl_item ON rail_bl.id = rail_bl_item.rail_bl_id WHERE rail_bl.creation_date > '01/01/2012' GROUP BY rail_bl.creation_date ORDER BY waybill.creation_date DESC I am able to get a two-column result containing the total weights for trucks on a given day and the total weights for rail on a given day in separate records, so my result looks like this: DATE | WEIGHT date1, truck_weight_for_date1 date1, rail_weight_for_date1 date2, truck_weight_for_date2 date2, rail_weight_for_date2 date3, truck_weight_for_date3 date3, rail_weight_for_date3 etc The snag I'm hitting is that I can't
Search Username Password Remember Me? Register Lost Password? facebook google twitter rss Free Web Developer Tools Advanced Search Forum Databases MS SQL Development Could not add the table '(' Thread: Could not add the table '(' Share This Thread Tweet This + 1 this Post To Linkedin Subscribe to this Thread Subscribe to This Thread January 8th, 2012,03:39 AM #1 No Profile Picture pg300 View Profile View Forum Posts Contributing User Devshed Newbie (0 - 499 posts) Join Date Aug 2006 Posts 47 Rep Power 11 Could not add the table '(' The query http://stackoverflow.com/questions/12412647/syntax-for-subqueries-in-the-from-clause-using-multiple-inner-joins-in-ms-query on the inside works but when I do a select from it then it throws the error: Code: Could not add the table '(' the queyr: Code: SELECT Eg.* FROM ( SELECT MAX(t.containername) , MAX(t.txndate) , MAX(t.growthrecipe) , w.epigroupid FROM eqdb_insite.dbo.dc_0200jdet AS t LEFT JOIN eqdb_insite.dbo.waferid AS w ON w.epiwaferid = t.containername WHERE t.containername LIKE 'X%' AND (t.growthrecipe LIKE 'ILMZA%' OR t.growthrecipe LIKE 'ILMZB%' OR t.growthrecipe LIKE http://forums.devshed.com/ms-sql-development-95/add-table-875247.html 'ILMZC%' OR t.growthrecipe LIKE 'ILMZD%') AND t.txndate>='12/25/11' AND t.txndate<'1/1/12' GROUP BY w.epigroupid ) AS Eg It works fine if I remove the select from ( ) AS Eg so if i just do this it works: Code: SELECT MAX(t.containername) , MAX(t.txndate) , MAX(t.growthrecipe) , w.epigroupid FROM eqdb_insite.dbo.dc_0200jdet AS t LEFT JOIN eqdb_insite.dbo.waferid AS w ON w.epiwaferid = t.containername WHERE t.containername LIKE 'X%' AND (t.growthrecipe LIKE 'ILMZA%' OR t.growthrecipe LIKE 'ILMZB%' OR t.growthrecipe LIKE 'ILMZC%' OR t.growthrecipe LIKE 'ILMZD%') AND t.txndate>='12/25/11' AND t.txndate<'1/1/12' GROUP BY w.epigroupid Faq Reply With Quote January 8th, 2012,04:23 AM #2 No Profile Picture shammat View Profile View Forum Posts Contributing User Devshed Frequenter (2500 - 2999 posts) Join Date Oct 2003 Location Germany Posts 2,803 Rep Power 350 Could not add the table '(' Is not a SQL Server error message. My guess is that whatever application you are using to run the SQL cannot handle derived table. Faq Reply With Quote January 15th, 2012,03:43 AM #3 No Profile Picture pg300 View Profile View Forum Posts Contributing User Devshed Newbie (0 - 499 posts) Join Date Aug 2006 Posts 47
Join INTELLIGENT WORK FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk http://www.tek-tips.com/viewthread.cfm?qid=1641000 With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search http://www.pcreview.co.uk/threads/ms-query-error-could-not-add-the-table.2518921/ One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips could not Posting Policies Jobs Jobs from Indeed What: Where: jobs by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft: Office Forum at Tek-Tips HomeForumsMIS/ITOffice SuitesMicrosoft: Office Forum MS query adding more could not add tables thread68-1641000 Forum Search FAQs Links MVPs MS query adding more tables MS query adding more tables colval (MIS) (OP) 9 Mar 11 05:03 HiI am doing an MSquery linking into an access databse to link into an excel spradsheet.An example databse as 37 records in all, all connceted to mulitple tables.when I add a table called Vlistcomponants the link then just shows 5 records.I am assuming this is because only 5 matching records have data in the vlistcomponant and the other 32 have nothing in it.I need to show all 37 records, I ahve tried to change the Join types but it says I cannot have the other join types as there are to many in the query.Here is the SQL code that works so far (see below).Any idea's how I can add the table in and see all 37 records.ThanksSELECT TableSites.SiteName, TableSites.SiteAddr1, TableSites.SiteSurveyorsName, TableSamples.SampleDate, TableSamples.SampleSurveyType, VListComponents.ItemValue, VListAreas.ItemValue, VListFloors.ItemValue, TableSamples.SampleRoomFROM TableSamples TableSamples, TableSites TableSites, VListAreas VListAreas, VListComponents VListComponents, VListFloors VListFloor
not add the table '\\lth01pc354\c$\Logs\History\TEST.CSV)' when running this subquery on an external data source: SELECT BEMIS_BAGGER_RATE AS 'RATE', (SELECT Avg(Val(BEMIS_BAGGER_RATE)) FROM \\lth01pc354\c$\Logs\History\TEST.CSV) AS 'AVERAGE' FROM \\lth01pc354\c$\Logs\History\TEST.CSV What am I doing wrong? Guest, Apr 26, 2006 #1 Advertisements Puppet_Sock Guest KHanna wrote: > I get the error "Could not add the table > '\\lth01pc354\c$\Logs\History\TEST.CSV)' when running this subquery on an > external data source: > > SELECT BEMIS_BAGGER_RATE AS 'RATE', (SELECT Avg(Val(BEMIS_BAGGER_RATE)) FROM > \\lth01pc354\c$\Logs\History\TEST.CSV) AS 'AVERAGE' > FROM \\lth01pc354\c$\Logs\History\TEST.CSV > > What am I doing wrong? Do you have all the appropriate permissions and write capabilities? Is the URL correct? I can't debug your sql for you. It's been a while. Can you do *any* sql through this link? Say you do the simplest possible select you can construct. Say, something like so. (Or whatever simple select should work.) SELECT BEMIS_BAGGER_RATE FROM \\lth01pc354\c$\Logs\History\TEST.CSV Does that work? That is, do the standard thing when something is broken. Try to do something as simple as you can and thus clear away complexity to uncover the source of the problem. Socks Puppet_Sock, Apr 26, 2006 #2 Advertisements Guest Guest The query works until I add the subquery portion. "Puppet_Sock" wrote: > KHanna wrote: > > I get the error "Could not add the table > > '\\lth01pc354\c$\Logs\History\TEST.CSV)' when running this subquery on an > > external data source: > > > > SELECT BEMIS_BAGGER_RATE AS 'RATE', (SELECT Avg(Val(BEMIS_BAGGER_RATE)) FROM > > \\lth01pc354\c$\Logs\History\TEST.CSV) AS 'AVERAGE' > > FROM \\lth01pc354\c$\Logs\History\TEST.CSV > > > > What am I doing wrong? > > Do you have all the appropriate permissions and write capabilities? > > Is the URL correct? > > I can't debug your sql for you. It's been a while. > > Can you do *any* sql through this link? Say you do the simplest > possible > select you can construct. Say, something like so. (Or whatever simple > select should work.) > > SELECT BEMIS_BAGGER_RATE > FROM \\lth01pc354\c$\Logs\History\TEST.CSV > > Does