Join two queries using MSQuery, returning into an Excel workbook

[potter_ricky]

In short I would like to join two queries using MSQuery, returning into an Excel workbook.

It’s the joining of the two queries that I’m struggling to figure out.

The underlying data comes from a workbook that has two sheets, each sheet containing a table. I cannot amend the workbook or sheets in any way.

The tables do have a relationship (date & source) but also have a unique field each which is not relevant to the other (bucket & booking ID).

Also a table may have a date and/or a source that the other doesn’t.

I would like to full join these tables ignoring their unique fields.

I have been able to write a query for each table that removes their unique fields, and then write a separate query that full joins the outputs.

What I would like to do is combine all three queries if possible.

My work so far:

Sheet 1 = ‘Data’:

Date	Source	Bucket	Cost (£)
30-Aug-12	Australia	B2	78.9694
30-Aug-12	Australia	B1	29.6856
05-Feb-13	Australia	B2	157.759
30-Aug-12	Malaysia	B1	1.426
05-Feb-13	Malaysia	B1	1.922
15-Feb-13	Malaysia	B2	1.178
30-Aug-12	Singapore	B1	2.976
05-Feb-13	Singapore	B2	60.45
05-Feb-13	Singapore	B1	28.272

<tbody>
</tbody>

Sheet 2 = ‘Data2’:

Booking ID	Date Booked	Commission GBP	Source
19861052	30/08/12	118.524375	Australia
21537304	05/02/13	36.2591328	Others
21537643	05/02/13	69.6276	Malaysia
21537681	05/02/13	105.60186	Malaysia
21537712	05/02/13	165.105216	Australia

<tbody>
</tbody>

Data Query:

SELECT FORMAT([Data$].[Date],'dd/mm/yyyy') AS [Date], [Data$].[Source] AS [Source], Sum([Data$].[Cost (£)]) AS [Cost]
FROM [\\Location\Data.xlsx].[Data$] [Data$]
GROUP BY [Data$].[Date], [Data$].[Source]
HAVING ([Data$].[Date] Is Not Null)

Data2 Query:

SELECT FORMAT([Data2$].[Date Booked],'dd/mm/yyyy') AS [Date], [Data2$].[Source] AS [Source], Sum([Data2$].[Commission GBP]) AS [Commission]
FROM [\\Location\Data.xlsx].[Data2$] [Data2$]
GROUP BY [Data2$].[Date Booked], [Data2$].[Source]
HAVING ([Data2$].[Date Booked] Is Not Null)

Full Join Query:

SELECT [Query1$].[Date] AS [Date], [Query1$].[Source] AS [Source], [Query1$].[Cost] AS [Cost], [Query2$].[Commission] AS [Commission]
FROM [\\Location\Output.xlsx].[Query1$] [Query1$] INNER JOIN [\\Location\Output.xlsx].[Query2$] [Query2$]
ON [Query1$].[Date] = [Query2$].[Date] AND [Query1$].[Source] = [Query2$].[Source]

UNION

SELECT [Query2$].[Date] AS [Date], [Query2$].[Source] AS [Source], [Query1$].[Cost] AS [Cost], [Query2$].[Commission] AS [Commission]
FROM [\\Location\Output.xlsx].[Query1$] [Query1$] RIGHT JOIN [\\Location\Output.xlsx].[Query2$] [Query2$]
ON [Query1$].[Date] = [Query2$].[Date] AND [Query1$].[Source] = [Query2$].[Source]
WHERE ([Query1$].[Date] IS NULL) AND ([Query1$].[Source] IS NULL)

UNION

SELECT [Query1$].[Date] AS [Date], [Query1$].[Source] AS [Source], [Query1$].[Cost] AS [Cost], [Query2$].[Commission] AS [Commission]
FROM [\\Location\Output.xlsx].[Query1$] [Query1$] LEFT JOIN [\\Location\Output.xlsx].[Query2$] [Query2$]
ON [Query1$].[Date] = [Query2$].[Date] AND [Query1$].[Source] = [Query2$].[Source]
WHERE ([Query2$].[Date] IS NULL) AND ([Query2$].[Source] IS NULL)

Output wanted:

Date	Source	Cost	Commission
05/02/2013	Australia	157.7589	165.1052
05/02/2013	Malaysia	1.922	175.2293
05/02/2013	Others		36.2591
05/02/2013	Singapore	88.722
15/02/2013	Malaysia	1.178
30/08/2012	Australia	108.6549	118.5243
30/08/2012	Malaysia	1.426
30/08/2012	Singapore	2.976

<tbody>
</tbody>

 

[J.Ty.]

Are you interested to get this done in Excel alone, without using MS Query?
It is possible to compute join in Excel.
J.Ty.

 

[potter_ricky]

Sure, to explain a little more:

As mentioned the data is in a workbook that I can't amend. The data I would be querying from this workbook is large, the file size is 40000KB.

I would like to query this data into a sheet in another workbook as a pivot table preferably.

The workbook to return into is likely to get quite large and calculation heavy, so where possible would like to limit its size and calculations, this is why I have opted to try and query the data using MSQuery and return as a pivot table.

I am using Excel 2013 64Bit on Win7 Pro 64Bit.

Thanks

 

[J.Ty.]

I am afraid that computing join of this size will be slow in Excel.

Anyway, I will prepare what you need later today - now I must go teaching students now.

J.Ty.

 

[Fazza]

hi

maybe like this?

SELECT [Date], Source, SUM(A.Cost) AS Cost, SUM(A.Commission) AS Commission
FROM (
SELECT [Date], Source, [Cost (£)] AS Cost, Null AS Commission
FROM [\\Location\Data.xlsx].[Data$]
UNION
SELECT [Date Booked], Source, Null AS Cost, [Commission GBP] AS Commission
FROM [\\Location\Data.xlsx].[Data2$]) A
GROUP BY [Date], Source

regards

 

[J.Ty.]

I have break now, so a few fast questions:

1) How do you represent NULL? Empty cell?
2) Tell me worksheet names and column letters of you input data
3) In Query1 and Query2 you have clauses "HAVING ([Data$].[Date] Is Not Null)" and "HAVING ([Data2$].[Date Booked] Is Not Null)", so in their results you do not have entires in which field Date is NULL. In this case the LEFT JOIN and RIGHT JOIN will produce nothing, because you have clauses "WHERE ([Query1$].[Date] IS NULL)"
and "WHERE ([Query2$].[Date] IS NULL)" there. Is this what you intended?


J.Ty.

 

[J.Ty.]

Hi,

The Excel solution is PROBABLY the following. Please bear in mind that your SQL queries are not exactly what you wanted in the output.

I assume your data to be located in Sheet1 and Sheet2.

Then make Sheet3 and Sheet4:

Excel 2010
	A	B	C	D
1	Date	Source	Cost	Commision
2	2012-08-30	Australia	108,655	118,5244
3	2012-08-30	Australia	108,655	118,5244
4	2013-02-05	Australia	157,759	165,1052
5	2012-08-30	Malaysia	1,426	NULL
6	2013-02-05	Malaysia	1,922	175,2295
7	2013-02-15	Malaysia	1,178	NULL
8	2012-08-30	Singapore	2,976	NULL
9	2013-02-05	Singapore	88,722	NULL
10	2013-02-05	Singapore	88,722	NULL
Sheet3

Cell Formulas
Range	Formula
A2	=Sheet1!A2
B2	=Sheet1!B2
C2	=SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2)
D2	=IF(COUNTIFS(Sheet2!B:B,A2,Sheet2!D:D,B2)=0,"NULL",SUMIFS(Sheet2!C:C,Sheet2!B:B,A2,Sheet2!D:D,B2))

Excel 2010

	A	B	C	D
1	Date	Source	Cost	Commision
2	2012-08-30	Australia	108,655	118,5244
3	2013-02-05	Others	NULL	36,25913
4	2013-02-05	Malaysia	1,922	175,2295
5	2013-02-05	Malaysia	1,922	175,2295
6	2013-02-05	Australia	157,759	165,1052

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula A2 =Sheet2!B2 B2 =Sheet2!D2 C2 =IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2)=0,"NULL",SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2)) D2 =SUMIFS(Sheet2!C:C,Sheet2!B:B,A2,Sheet2!D:D,B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You should fill Sheet1 with formulas for as many rows as Sheet1 has, and Sheet4 for as many rows as Sheet2 has.

Then copy values from Sheet3 and Sheet4 to a common Sheet5 as values, and finally request removal of duplicates from the Data menu on the ribbon, specifying that duplicates are recognized by columns Date and Source.

That's it.

Please let me know if everything is OK.

J.Ty.