Using multiple Joins in MS Query

[dragonb]

Hi,

I have been trying to write a MS Query in MS Excel to retrieve data.
DataV and DQ sheets are from the same work book. The expected output is in a new work book. I have done the connectivity between two work books, importing etc. Need help on writing MSQuery to retrieve the data.
When executing the below query, I am getting "Could not add the table (SELECT"

SELECT V1.*,V2.* FROM
(SELECT DataV.C1, Sum(DataV.C3)
FROM `C:\Users\user\Desktop\Data.xlsx`.DataV DataV
GROUP BY DataV.C1) AS V1
LEFT OUTER JOIN
(SELECT DataV.C1, Sum(DataV.C3)
FROM `C:\Users\user\Desktop\Data.xlsx`.DataV DataV
WHERE (DataV.C2='Y')
GROUP BY DataV.C1) AS V2
ON V1.C1=V2.C2

Sample Input:

DataV sheet:
[TABLE="width: 192"]
<tbody>[TR]
[TD]Col1[/TD]
[TD="width: 64"]Col2[/TD]
[TD="width: 64, align: right"]Col3[/TD]
[/TR]
[TR]
[TD="class: xl63"]T01[/TD]
[TD="class: xl64"]N[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]T02[/TD]
[TD="class: xl67"]N[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]T03[/TD]
[TD="class: xl64"]N[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]T04[/TD]
[TD="class: xl67"]N[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]T01[/TD]
[TD="class: xl64"]Y[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl66"]T04[/TD]
[TD="class: xl67"]Y[/TD]
[TD="class: xl68, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63"]T01[/TD]
[TD="class: xl64"]Y[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

DQ sheet:
[TABLE="width: 128"]
<tbody>[TR]
[TD]Col1[/TD]
[TD="width: 64"]Col2[/TD]
[/TR]
[TR]
[TD]T01[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]T02[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]T03[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]T04[/TD]
[TD]K[/TD]
[/TR]
</tbody>[/TABLE]


Expected Output:

[TABLE="width: 298"]
<tbody>[TR]
[TD]Col1[/TD]
[TD]Sum(Total)[/TD]
[TD="align: right"]Sum(Col2='Y')[/TD]
[TD="align: right"]DQ[/TD]
[/TR]
[TR]
[TD]T01[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]A[/TD]
[/TR]
[TR]
[TD]T02[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Null[/TD]
[TD="align: right"]B[/TD]
[/TR]
[TR]
[TD]T03[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]Null[/TD]
[TD="align: right"]F[/TD]
[/TR]
[TR]
[TD]T04[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]K[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in Advance

 

[Fazza]

Hello

A quick glance shows a problem with the last line, ON V1.C1 = V2.C2

V2 doesn't have a C2 field

I haven't looked at anything else. If there is still a problem I will though. Please advise

regards

 

[dragonb]

Sorry that was a typo error
It should combine with C1.

SELECT V1.*,V2.* FROM (SELECT DataV.C1, Sum(DataV.C3) FROM `C:\Users\user\Desktop\Data.xlsx`.DataV DataV GROUP BY DataV.C1) AS V1 LEFT OUTER JOIN (SELECT DataV.C1, Sum(DataV.C3) FROM `C:\Users\user\Desktop\Data.xlsx`.DataV DataV WHERE (DataV.C2='Y') GROUP BY DataV.C1) AS V2 ON V1.C1=V2.C1</pre>