Another Excel Issue (Summary Sheets?) 1

[LizzyAnn]

Ok, so I just got through the wonders of doing a query (from thread68-1527412) Worked out quite lovely. But apparently, it's not good enough for the higher-ups (My partner-in-crime and myself found it to be quite wonderful.)

So here's the situation: I now have one workbook with multiple tabs that all update from their corresponding workbooks. (Which everyone decided was brilliant. Thanks, Skip.) The question is, is there anyway to create a new tab in the "master" workbook that will list the data of all the "linked" tabs? I've tried consolidating, and I can't get a query to line up all the columns.

Extra info: There are no numbers, just names, addresses, etc. And each spreadsheet has the same columns.

 

[SkipVought]

New sheet for the JOIN of the three tables (sheets).

Insert a query data > import external data...

Add all three tables in the query grid.

drag the fields from ONLY ONE table into the grid.

Hit the SQL tool button, copy the SQL code, and post back with your code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

That.... didn't work.

I have columns... and it pulled the data...
but it copied each line of each spreadsheet over hundreds of times... (So now line 1 of spreadsheet 1 is lines 1-1205) it did it for every line...

Now I'm confused...

 

[SkipVought]

Take a breath!

Tell me what did not work. Forget copying rows 100 times!!!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Ok, I made it to the query window where it shows me the three tables and whatnot. And I dragged the columns from one table down, and went back to excel.. and that gave me column headers with no info. When I dragged the columns into each other before dragging one down.. it gave me the thousand copies of each line...

So I'm kinda stuck...

 

[SkipVought]

"I dragged the columns from one table down, and went back to excel.."

Tha is not what I asked you to do.

After showing all three tables and dragging the columns from one of the tables into the query grid, hit the SQL button, copy the SQL code and post back. You can File > Return data to Excel but that is not what I am after. I need to see the SQL code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Oh... well that explains a lot... (whoops... I guess this is what I get for not having any coffee this morning)

Let's try that part again.
Now I have this:

SELECT `Sheet1$`.`Address `, `Sheet1$`.City, `Sheet1$`.`Company Name `, `Sheet1$`.`Customer Name`, `Sheet1$`.`Phone Number`, `Sheet1$`.`Sales Rep`, `Sheet1$`.Zip
FROM `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet1$` `Sheet1$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet2$` `Sheet2$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet3$` `Sheet3$`


Which I assume makes more sense...

 

[SkipVought]

Copy this, edit the querytable you just did, hit the SQL button and replace the contents with this. Then, File > Return data to Excel

Nots that Each separate JOIN has a 'Source' like 'Sheet1'. You can substitute whatever you want, and if you do not need to know the source, just delete these three entries.

SELECT
  `Address `
, City
, `Company Name `
, `Customer Name`
, `Phone Number`
, `Sales Rep`
, Zip[b]
, 'Sheet1' as 'Source'[/b]

FROM
  `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet1$` 

JOIN

SELECT
  `Address `
, City
, `Company Name `
, `Customer Name`
, `Phone Number`
, `Sales Rep`
, Zip[b]
, 'Sheet2'[/b]

FROM
  `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet2$` 

JOIN

SELECT
  `Address `
, City
, `Company Name `
, `Customer Name`
, `Phone Number`
, `Sales Rep`
, Zip[b]
, 'Sheet3'[/b] 

FROM
 `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet3$`

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Syntax error in FROM clause" ??

 

[SkipVought]

try this

SELECT
  `Address `
, City
, `Company Name `
, `Customer Name`
, `Phone Number`
, `Sales Rep`
, Zip
, 'Sheet1'

FROM
  `Sheet1$` 

JOIN

SELECT
  `Address `
, City
, `Company Name `
, `Customer Name`
, `Phone Number`
, `Sales Rep`
, Zip
, 'Sheet2'

FROM
  `Sheet2$` 

JOIN

SELECT
  `Address `
, City
, `Company Name `
, `Customer Name`
, `Phone Number`
, `Sales Rep`
, Zip
, 'Sheet3' 

FROM
 `Sheet3$`

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Same response...

 

[SkipVought]

duh. Sorry!

Trying to juggle too many balls this AM.

SELECT `Address `, City, `Company Name `, `Customer Name`, `Phone Number`, `Sales Rep`, Zip, 'Sheet1' as 'Source'

FROM `Sheet1$`

UNION

SELECT `Address `, City, `Company Name `, `Customer Name`, `Phone Number`, `Sales Rep`, Zip, 'Sheet2'

FROM `Sheet2$`

UNION

SELECT `Address `, City, `Company Name `, `Customer Name`, `Phone Number`, `Sales Rep`, Zip, 'Sheet3'

FROM `Sheet3$`

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

So.... that fact that eventually I'm going to have to get it total about 9 spreadsheets probably should've come into play earlier, huh?

On the bright side, new error message "To few parameters. Expected 1.

 

[SkipVought]

"To few parameters. Expected 1."

means that in one of your sheets, ONE heading is different.

You may have to "strip" down the SQL to ONE sheet at a time to "debug" where the difference is.

FYI, You have trailing spaces (or some non-printable character) in several of you headings as in `Address ` You may not be aboe to clean that up, as you are dealing with data that comes from an external source.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

With one it can't add the table.

 

[LizzyAnn]

Scratch that... apparently you can't have multiple "outer" joins (Isn't that what we were doing?)


.... now what?

 

[SkipVought]

In the Query Editor hit File > New
Select the Excel driver and drill down to your workbook.

Select the Sheet that can't be added. Drag all the fields into the grid. Copy the SQL. Post your code.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

I keep getting FROM errors...

 

[SkipVought]

Start from scratch and access the sheet in question.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Did that. I can convince it to do each sheet separately, it's getting it to do all of them that's the issue.

I can't believe there's not some button somewhere that does this for me..