Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Revisit an older post, looking for SQL help on adding range data frommany sheets to a single sheet
This post answers the question, but I can't figure out how to apply
the model displayed to my scenario. http://groups.google.com/group/micro...b4836984fd768e I created named ranges, and get to the point of: "Replace the displayed SQL code with an adapted version of this:", Now, all bets are off. My named ranges on the 4 worksheets a BG Chicago Lisle Schaumburg This is the code that appears in the SQL query window: SELECT BG.`Most Recent Employer`, BG.`Start Date`, BG.`Last Name`, BG.`First Name`, BG.Title, BG.`Previous Employer`, BG.Title1, BG.`Email Address`, BG.`BBB Office`, BG.Consultant FROM BG BG ORDER BY BG.`Most Recent Employer` How do I create as the author describes as "an adaped version of this"? I'd like to get it to work. but how should the SQL code look with the additional named ranges added? Having trouble figuring out the nomenclature. Many thanks, for anyone who'd like to have a gander at this. Rick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Revisit an older post, looking for SQL help on adding range data f
I guess you're using a SQL query to manipulate data via Data, Import
External Data, New Database Query, right? I've fooled with this only a few hours' worth, but IIRC a named range in Excel can serve as a table name in such a query. In a SQL query the table name is the part that comes after the FROM clause. Ok, table names can appear elsewhere, but in a basic query it's the word after FROM, which in the below example is "BG". So you're pulling data from the named range "BG" and getting ready to put it somewhere else. Whoever wrote those instructions apparently supposed you know enough SQL to write or at least "adapt" your own queries. If all you were missing was the connection between Excel named ranges and SQL tables, maybe you do. But if you don't savvy SQL, you'll need someone to get you over the hum. Do you want to pursue that angle, or do you know where you are now? --- "Rick" wrote: This post (http://groups.google.com/group/micro...xcel.worksheet. functions/browse_frm/thread/6fb68debb1f35f9b/75b4836984fd768e?hl=en &lnk=gst&q=comcine+data+from+several+worksheets#75 b4836984fd768e) answers the question, but I can't figure out how to apply the model displayed to my scenario. I created named ranges, and get to the point of: "Replace the displayed SQL code with an adapted version of this:". Now, all bets are off. My named ranges on the 4 worksheets a BG Chicago Lisle Schaumburg This is the code that appears in the SQL query window: SELECT BG.`Most Recent Employer`, BG.`Start Date`, BG.`Last Name`, BG.`First Name`, BG.Title, BG.`Previous Employer`, BG.Title1, BG.`Email Address`, BG.`BBB Office`, BG.Consultant FROM BG BG ORDER BY BG.`Most Recent Employer` How do I create as the author describes as "an adaped version of this"? I'd like to get it to work. but how should the SQL code look with the additional named ranges added? Having trouble figuring out the nomenclature. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Revisit an older post, looking for SQL help on adding range dataf
On May 31, 11:30*am, Bob Bridges
wrote: I guess you're using a SQL query to manipulate data via Data, Import External Data, New Database Query, right? *I've fooled with this only a few hours' worth, but IIRC a named range in Excel can serve as a table name in such a query. *In a SQL query the table name is the part that comes after the FROM clause. *Ok, table names can appear elsewhere, but in a basic query it's the word after FROM, which in the below example is "BG". *So you're pulling data from the named range "BG" and getting ready to put it somewhere else. Whoever wrote those instructions apparently supposed you know enough SQL to write or at least "adapt" your own queries. *If all you were missing was the connection between Excel named ranges and SQL tables, maybe you do. *But if you don't savvy SQL, you'll need someone to get you over the hum. *Do you want to pursue that angle, or do you know where you are now? --- "Rick" wrote: This post (http://groups.google.com/group/micro...xcel.worksheet. functions/browse_frm/thread/6fb68debb1f35f9b/75b4836984fd768e?hl=en &lnk=gst&q=comcine+data+from+several+worksheets#75 b4836984fd768e) answers the question, but I can't figure out how to apply the model displayed to my scenario. I created named ranges, and get to the point of: "Replace the displayed SQL code with an adapted version of this:". *Now, all bets are off. My named ranges on the 4 worksheets a BG Chicago Lisle Schaumburg This is the code that appears in the SQL query window: SELECT BG.`Most Recent Employer`, BG.`Start Date`, BG.`Last Name`, BG.`First Name`, BG.Title, BG.`Previous Employer`, BG.Title1, BG.`Email Address`, BG.`BBB Office`, BG.Consultant FROM BG BG ORDER BY BG.`Most Recent Employer` How do I create as the author describes as "an adaped version of this"? *I'd like to get it to work. but how should the SQL code look with the additional named ranges added? *Having trouble figuring out the nomenclature.- Hide quoted text - - Show quoted text - Bob, you nailed that one. No, I'm not adept at SQL codes. I've named the ranges, so need to add the other 3 named ranges to the query so it'll pull in from those places as well. They're identical in structure, and all include the column labels. Pulling in from BG works fine (I've added it to an additional sheet when prompted.) I don't know enough (yet) to understand the relationship between the named ranges, and how they're defined in the SQL query. I tried duplicating the results of the BG query, and replacing and adding the other names. I thought it was worth a shot, but couldn't hit on the right combination. Thanks for any tips, or suggestions. Bob, if you know of another way (. . .aside from SQL import) to bring in data from the other tabs without copying and pasting, I'd certainly be amenable to an approach that may be effective. Thanks again. Rick |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Revisit an older post, looking for SQL help on adding range datafrom many sheets to a single sheet
On 31 May, 15:03, Rick wrote:
This post answers the question, but I can't figure out how to apply the model displayed to my scenario. http://groups.google.com/group/micro...orksheet.funct... I created named ranges, and get to the point of: "Replace the displayed SQL code with an adapted version of this:", Now, all bets are off. My named ranges on the 4 worksheets a BG Chicago Lisle Schaumburg This is the code that appears in the SQL query window: SELECT BG.`Most Recent Employer`, BG.`Start Date`, BG.`Last Name`, BG.`First Name`, BG.Title, BG.`Previous Employer`, BG.Title1, BG.`Email Address`, BG.`BBB Office`, BG.Consultant FROM BG BG ORDER BY BG.`Most Recent Employer` How do I create as the author describes as "an adaped version of this"? I'd like to get it to work. but how should the SQL code look with the additional named ranges added? *Having trouble figuring out the nomenclature. Many thanks, for anyone who'd like to have a gander at this. Rick Rick, Stripping it right back to the bare bones, this might get you started: SELECT * FROM BG Union All SELECT * FROM Chicago Union All SELECT * FROM Lisle Union All SELECT * FROM Schaumburg I always add a "Source" column to show where the original data came from. The SQL for this would look like: SELECT *, 'BG' as Source FROM BG Union All SELECT *, 'Chicago' as Source FROM Chicago Union All SELECT *, 'Lisle' as Source FROM Lisle Union All SELECT *, 'Schaumburg' as Source FROM Schaumburg Hope this helps. Ian |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Revisit an older post, looking for SQL help on adding range datafrom many sheets to a single sheet
On May 31, 2:29*pm, wrote:
On 31 May, 15:03, Rick wrote: This post answers the question, but I can't figure out how to apply the model displayed to my scenario. http://groups.google.com/group/micro...orksheet.funct... I created named ranges, and get to the point of: "Replace the displayed SQL code with an adapted version of this:", Now, all bets are off. My named ranges on the 4 worksheets a BG Chicago Lisle Schaumburg This is the code that appears in the SQL query window: SELECT BG.`Most Recent Employer`, BG.`Start Date`, BG.`Last Name`, BG.`First Name`, BG.Title, BG.`Previous Employer`, BG.Title1, BG.`Email Address`, BG.`BBB Office`, BG.Consultant FROM BG BG ORDER BY BG.`Most Recent Employer` How do I create as the author describes as "an adaped version of this"? I'd like to get it to work. but how should the SQL code look with the additional named ranges added? *Having trouble figuring out the nomenclature. Many thanks, for anyone who'd like to have a gander at this. Rick Rick, Stripping it right back to the bare bones, this might get you started: SELECT * FROM BG Union All SELECT * FROM Chicago Union All SELECT * FROM Lisle Union All SELECT * FROM Schaumburg I always add a "Source" column to show where the original data came from. *The SQL for this would look like: SELECT *, 'BG' as Source FROM BG Union All SELECT *, 'Chicago' as Source FROM Chicago Union All SELECT *, 'Lisle' as Source FROM Lisle Union All SELECT *, 'Schaumburg' as Source FROM Schaumburg Hope this helps. Ian- ian: That worked just wonderfully! Thank you so much. Rick. Now it's time to brush up on my SQL. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate Column C in multiple sheets into single sheet. | Excel Discussion (Misc queries) | |||
selecting a single sheet from a volume of sheets in a workbook | Excel Worksheet Functions | |||
Multiple sheets as data for a single sheet | Excel Worksheet Functions | |||
Adding a Single Cell total from Seperate sheets | Excel Worksheet Functions | |||
Create a single sheet from many sheets | Excel Worksheet Functions |