Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to craete a MS Query
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to craete a MS Query
hi
create the query(s) first(before the pivot table) on the menu bar... dataimport external datanew database query... follow the wizard. regards FSt1 "JR Hester" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to craete a MS Query
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 DataRefresh 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) "JR Hester" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to craete a bar chart in excell | Charts and Charting in Excel | |||
craete a print buffer in execl | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |