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’:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Source
[/TD]
[TD]Bucket
[/TD]
[TD]Cost (£)
[/TD]
[/TR]
[TR]
[TD]30-Aug-12
[/TD]
[TD]Australia
[/TD]
[TD]B2
[/TD]
[TD]78.9694
[/TD]
[/TR]
[TR]
[TD]30-Aug-12
[/TD]
[TD]Australia
[/TD]
[TD]B1
[/TD]
[TD]29.6856
[/TD]
[/TR]
[TR]
[TD]05-Feb-13
[/TD]
[TD]Australia
[/TD]
[TD]B2
[/TD]
[TD]157.759
[/TD]
[/TR]
[TR]
[TD]30-Aug-12
[/TD]
[TD]Malaysia
[/TD]
[TD]B1
[/TD]
[TD]1.426
[/TD]
[/TR]
[TR]
[TD]05-Feb-13
[/TD]
[TD]Malaysia
[/TD]
[TD]B1
[/TD]
[TD]1.922
[/TD]
[/TR]
[TR]
[TD]15-Feb-13
[/TD]
[TD]Malaysia
[/TD]
[TD]B2
[/TD]
[TD]1.178
[/TD]
[/TR]
[TR]
[TD]30-Aug-12
[/TD]
[TD]Singapore
[/TD]
[TD]B1
[/TD]
[TD]2.976
[/TD]
[/TR]
[TR]
[TD]05-Feb-13
[/TD]
[TD]Singapore
[/TD]
[TD]B2
[/TD]
[TD]60.45
[/TD]
[/TR]
[TR]
[TD]05-Feb-13
[/TD]
[TD]Singapore
[/TD]
[TD]B1
[/TD]
[TD]28.272
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2 = ‘Data2’:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Booking ID
[/TD]
[TD]Date Booked
[/TD]
[TD]Commission GBP
[/TD]
[TD]Source
[/TD]
[/TR]
[TR]
[TD]19861052
[/TD]
[TD]30/08/12
[/TD]
[TD]118.524375
[/TD]
[TD]Australia
[/TD]
[/TR]
[TR]
[TD]21537304
[/TD]
[TD]05/02/13
[/TD]
[TD]36.2591328
[/TD]
[TD]Others
[/TD]
[/TR]
[TR]
[TD]21537643
[/TD]
[TD]05/02/13
[/TD]
[TD]69.6276
[/TD]
[TD]Malaysia
[/TD]
[/TR]
[TR]
[TD]21537681
[/TD]
[TD]05/02/13
[/TD]
[TD]105.60186
[/TD]
[TD]Malaysia
[/TD]
[/TR]
[TR]
[TD]21537712
[/TD]
[TD]05/02/13
[/TD]
[TD]165.105216
[/TD]
[TD]Australia
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Source
[/TD]
[TD]Cost
[/TD]
[TD]Commission
[/TD]
[/TR]
[TR]
[TD]05/02/2013
[/TD]
[TD]Australia
[/TD]
[TD]157.7589
[/TD]
[TD]165.1052
[/TD]
[/TR]
[TR]
[TD]05/02/2013
[/TD]
[TD]Malaysia
[/TD]
[TD]1.922
[/TD]
[TD]175.2293
[/TD]
[/TR]
[TR]
[TD]05/02/2013
[/TD]
[TD]Others
[/TD]
[TD][/TD]
[TD]36.2591
[/TD]
[/TR]
[TR]
[TD]05/02/2013
[/TD]
[TD]Singapore
[/TD]
[TD]88.722
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/02/2013
[/TD]
[TD]Malaysia
[/TD]
[TD]1.178
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/08/2012
[/TD]
[TD]Australia
[/TD]
[TD]108.6549
[/TD]
[TD]118.5243
[/TD]
[/TR]
[TR]
[TD]30/08/2012
[/TD]
[TD]Malaysia
[/TD]
[TD]1.426
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/08/2012
[/TD]
[TD]Singapore
[/TD]
[TD]2.976
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Date	Source	Cost	Commision
Australia
Others	NULL
Malaysia
Malaysia
Australia

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2012-08-30[/TD]

[TD="align: right"]108,655[/TD]
[TD="align: right"]118,5244[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2013-02-05[/TD]

[TD="align: right"]36,25913[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2013-02-05[/TD]

[TD="align: right"]1,922[/TD]
[TD="align: right"]175,2295[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2013-02-05[/TD]

[TD="align: right"]1,922[/TD]
[TD="align: right"]175,2295[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2013-02-05[/TD]

[TD="align: right"]157,759[/TD]
[TD="align: right"]165,1052[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]=Sheet2!B2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=Sheet2!D2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=IF(COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,B2)=0,"NULL",SUMIFS(Sheet1!D:D,Sheet1!A:A,A2,Sheet1!B:B,B2))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMIFS(Sheet2!C:C,Sheet2!B:B,A2,Sheet2!D:D,B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.