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:

Col1	Col2	Col3
T01	N	1
T02	N	1
T03	N	1
T04	N	1
T01	Y	1
T04	Y	1
T01	Y	3

<tbody>
</tbody>

DQ sheet:

Col1	Col2
T01	A
T02	B
T03	F
T04	K

<tbody>
</tbody>

Expected Output:

Col1	Sum(Total)	Sum(Col2='Y')	DQ
T01	5	4	A
T02	1	Null	B
T03	1	Null	F
T04	2	1	K

<tbody>
</tbody>

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>