Revisit an older post, looking for SQL help on adding range data frommany sheets to a single sheet

[Rick]

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/micr...data+from+several+worksheets#75b4836984fd768e

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 are:
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

 

[Bob Bridges]

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]

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 afew
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?








- 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

 

[browni]

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/microsoft.public.excel.worksheet.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 are:
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

 

[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.