Hello all
I am trying to transform a query as need to create a customisable graph in report, I got other 2 working but struggling with this one.
Here are my 2 base queries:
Q_YTDmonthlyAREAchart3_sub1
Q_YTDmonthlyAREAchart3_sub2
This is third query a combination of those 2 above, works ok as it should:
This is my attempt to transform it, but gives me error message:
The error message:
You tried to execute a query that does not include the specified expression
'Area' as part of an aggregate function.
Thank you in advance.
I am trying to transform a query as need to create a customisable graph in report, I got other 2 working but struggling with this one.
Here are my 2 base queries:
Q_YTDmonthlyAREAchart3_sub1
SELECT Year(dDateFound) AS Y, Month([dDateFound]) AS M, Q_Defects.aName AS Area, Sum(IIf([dDateFound] is not null,1,0)) AS [Found]
FROM Q_Defects
WHERE (dDeptFK=Forms!F_MainMenu.cboSelectDept OR Forms!F_MainMenu!cboSelectDept IS NULL)
AND (dAreaFK=Forms!F_MainMenu.cboStatsArea OR Forms!F_MainMenu!cboStatsArea IS NULL)
AND (dShiftFK=Forms!F_MainMenu.cboStatsShift OR Forms!F_MainMenu!cboStatsShift IS NULL)
GROUP BY Year(dDateFound), Month([dDateFound]), Q_Defects.aName;
Q_YTDmonthlyAREAchart3_sub2
SELECT Year([dDateClosed]) AS Y, Month([dDateClosed]) AS M, Q_Defects.aName AS Area, Sum(IIf([dDateClosed] is not null,1,0)) AS Fixed
FROM Q_Defects
WHERE (dDeptFK=Forms!F_MainMenu.cboSelectDept OR Forms!F_MainMenu!cboSelectDept IS NULL)
AND (dAreaFK=Forms!F_MainMenu.cboStatsArea OR Forms!F_MainMenu!cboStatsArea IS NULL)
AND (dShiftFK=Forms!F_MainMenu.cboStatsShift OR Forms!F_MainMenu!cboStatsShift IS NULL)
GROUP BY Year([dDateClosed]), Month([dDateClosed]), Q_Defects.aName;
This is third query a combination of those 2 above, works ok as it should:
SELECT Q_YTDmonthlyAREAchart3_sub1.Y, Q_YTDmonthlyAREAchart3_sub1.M, Q_YTDmonthlyAREAchart3_sub1.Area, Q_YTDmonthlyAREAchart3_sub1.Found, Q_YTDmonthlyAREAchart3_sub2.Fixed, round(100*([Fixed]/[Found]),0) AS Completed
FROM Q_YTDmonthlyAREAchart3_sub1 LEFT JOIN Q_YTDmonthlyAREAchart3_sub2 ON (Q_YTDmonthlyAREAchart3_sub1.Y = Q_YTDmonthlyAREAchart3_sub2.Y) AND (Q_YTDmonthlyAREAchart3_sub1.M = Q_YTDmonthlyAREAchart3_sub2.M) AND (Q_YTDmonthlyAREAchart3_sub1.Area = Q_YTDmonthlyAREAchart3_sub2.Area);
This is my attempt to transform it, but gives me error message:
The error message:
You tried to execute a query that does not include the specified expression
'Area' as part of an aggregate function.
TRANSFORM Completed
SELECT M, Area
FROM Q_YTDmonthlyAREAchart3
GROUP BY M, Area
PIVOT M
In (1,2,3,4,5,6,7,8,9,10,11,12);
Thank you in advance.