How to craete a MS Query

[JR Hester]

Following a thread from back in 06, I found Debra Dalgliesh's pointers on
cretaing a pivot table from multiple data ranges. MAybe I am brain dead at
this point on Friday afternoon, but I can't seem to stumble onto teh proper
way to generate a MS Query. I start the pivot atble wizard and get to the
datasource/Queries/OLAP cubes. Opening the Queries tab does not give me any
option to create query text, what am I missing?3

Using Excel 2002 on WIN2002

Thanks for your support

 

[FSt1]

hi
create the query(s) first(before the pivot table)
on the menu bar...
data>import external data>new database query...
follow the wizard.

regards
FSt1

 

[Ron Coderre]

If you want to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts:

This example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each wkbk must be in named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
....ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)<Data><Import External Data><New Database Query>
• Databases: Excel Files

Browse to ONE of the files, pick the data range to import.
--->Accept defaults until the next step.

At The last screen select the View data/Edit the Query option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Dept1111`.rng1111Data
UNION ALL
SELECT * FROM `C:\Dept2222`.rng2222Data
UNION ALL
SELECT * FROM `C:\Dept3333`.rng3333Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng4444Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng5555Data

(Note: the apostrophes in the SQL code ( ` )are located on the same key as
the tilde (~) )
Return the data to Excel.

Once that is done....to get the latest data just click in the data range
then Data>Refresh Data.
(You can edit the query at any time to add/remove data sources and/or
fields.)


Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)