joining multiple Sql ODBCs into one table

[ricardomadaleno]

Good morning,

I'm new to working with SQL ODBCs and Microsoft Query so I need a little help.

I have a workbook with several tables each one connected through an ODBCs to an SQL database...

They all look the same (same headers and type of information) but are stored in diferent databases for operational purposes.

Here's and example of the tables I have:
[TABLE="class: grid, width: 500"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Manager[/TD]
[TD]Project[/TD]
[TD]Date[/TD]
[TD]% Completed[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 1[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 2[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 3[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 4[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 5[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]21%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]Project[/TD]
[TD]Date[/TD]
[TD]% Completed[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 11[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 12[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 13[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 14[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 15[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]21%[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]Project[/TD]
[TD]Date[/TD]
[TD]% Completed[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 6[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 7[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 8[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 9[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 10[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]21%[/TD]
[/TR]
</tbody>[/TABLE]

you get the idea...

So right now, i have each of these tables in it's separate sheet, and each table connected through it's ODBC.

What I would like to do is "join" all tables in one. Like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Manager[/TD]
[TD]Project[/TD]
[TD]Date[/TD]
[TD]% Completed[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 1[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 2[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 3[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 4[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Manager 1[/TD]
[TD]Project 5[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]21%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 11[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 12[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 13[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 14[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Manager 2[/TD]
[TD]Project 15[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]21%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 6[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]55%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 7[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]75%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 8[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]85%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 9[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]63%[/TD]
[/TR]
[TR]
[TD]Manager 3[/TD]
[TD]Project 10[/TD]
[TD="align: right"]12-06-2015[/TD]
[TD="align: right"]21%[/TD]
[/TR]
</tbody>[/TABLE]


Is it possible?

I'm using Microsoft Office 2013 and I don't have access to the sql server management studio, so I would have to do it either in excel directly, or at the most through Microsoft Query.

Can anyone help?

Thank you very much, and sorry for the long post.

 

[Fazza]

Likely do-able in MS Query. Try putting the more details straight into the SQL. I don't remember specific syntax (google might find examples), but something like

SELECT your fields
FROM `details of first connection/data source`.table
UNION ALL
SELECT your fields
FROM `details of second connection/data source`.table
UNION ALL
SELECT your fields
FROM `details of third connection/data source`.table
UNION ALL
SELECT your fields
FROM `details of fourth connection/data source`.table

If you can't get that to work, and I really think it will be possible, suggest using VBA & individual ADO queries loading returned recordsets directly to destination worksheet one under another.

HTH

 

[Fazza]

Here is one old thread that might lead to a solution, http://www.mrexcel.com/forum/excel-questions/378209-ado-union-data-excel-data-text-file.html

I'm sure there are old threads that give examples more like your question but I didn't find them after looking just now.

regards

 

[ricardomadaleno]

Hi Fazza,

I'm sorry for the late reply, but I was only able to test this properly today.

I'm very glad to say that it worked beautifully :D thank you so much.

I have two follow up questions:

1- I can't seem to be able to be able to rename the columns using the method: SELECT your fields as 'new column name'

is there any other way? I'm changing the same column in all the ODBCs and giving always the same name...


2- is it possible to add a column that only exists in some of the ODBCs? even if in the ones it would be appear blank or NULL...

everytime i try I get the message: "All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Statements(s) could not be prpared."


Thank you for all your help.

 

[ricardomadaleno]

Hi,

So I've managed to solve the first issue...I just added a column stating 'NULL' to all the ODBCs that didn't have the extra column

But i just can't seem to solve the second issue...

I've read that the column renaming should only be on the first section of the query, so I've tried it but the header still shows as empty. I've tried putting the name on all sections of the query, still empty... Also tried putting the new name between 'new name', "new name" or even without quotation... but it always shows as empty...

Here is an example of my code:

SELECT 'Project_1' AS 'Project',
ODBC_Project_1.id,
ODBC_Project_1.manager,
ODBC_Project_1.area,
ODBC_Project_1.date,
'-' as 'Segment',
convert(float,ODBC_Project_1.completion)*0.01 AS Completion,
Day(date) AS 'Day',
month(date) AS 'Month',
year(date) AS 'Year'

FROM odbc.dbo.ODBC_Project_1

WHERE (year(date)>2014)

UNION ALL

SELECT 'Project_2',
ODBC_Project_2.id,
ODBC_Project_2.Manager,
ODBC_Project_2.area,
ODBC_Project_2.date,
ODBC_Project_2.segment,
convert(float,ODBC_Project_2.completion)*0.01,
Day(date),
month(date),
year(date)


FROM odbc.dbo.ODBC_Project_2

WHERE (year(date)>2014)

UNION ALL

SELECT 'NETONE',
ODBC_Project_3.id,
ODBC_Project_3.Manager,
ODBC_Project_3.area,
ODBC_Project_3.date,
'-',
convert(float,ODBC_Project_3.completion)*0.01,
Day(date),
month(date),
year(date)

FROM odbc.dbo.ODBC_Project_3

WHERE (year(date)>2014)

Can anyone help?

Thanks

 

[Fazza]

hi. I'm short of time. A guick reading of the question confused me a little, sorry. I'm not sure exactly what the question is. Can you try again, please?

Basic ideas,

SELECT some_field AS [New Name Inside Brackets Is Most Robust], Null AS [New Field Not in Source Data], hope_this_helps
FROM source_data

Be careful with (best to avoid) field names like Date, Day, Month and Year
As these are functions. Instead names like ActivityDate, BudgetYear, etc

 

[ricardomadaleno]

Hi Fazza,

Thanks again for your help... the square brackets solved the issue

Also, thank you for the tips regarding the names Day, Month, Year and Date...

I still have a lot to learn.

Thank you so much for all your help.

 

[Fazza]

You're very welcome, Ricardo. Best regards, Fazza