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"
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
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"
Code:
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