Need help to transform a query (1 Viewer)

[Misiek]

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

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.

 

[JHB]

Do you have a query/table with the name "Q_YTDmonthlyAREAchart3"?

 

[Misiek]

Yes it's a query. The third query in previous post is the one.

This error message, what does it actually mean?
Is it just something simple like a bracket or comma or name or is it something serious that access doesn't understand how to deal with?

 

[JHB]

You could try the below:

TRANSFORM Completed
SELECT M, Area
FROM Q_YTDmonthlyAREAchart3
GROUP BY M
PIVOT M
In (1,2,3,4,5,6,7,8,9,10,11,12);

 

[Misiek]

No, still the same error:

You tried to execute a query that does not include the specified expression
'1' as part of an aggregate function.

:/

 

[JHB]

Could you post your database with some sample data in it, zip it?

 

[Misiek]

Yes I can, please see attached.

 

[JHB]

Is the attached what you're looking for?

 

[Misiek]

Yes, Thats almost perfect.

How to assign the one colour for specific area like in my charts above when only department elected, and how mohave only one colour for all of then when area selected?

Thank you

 

[JHB]

Database attached.

 

[Misiek]

Whhhoooooaaaa perfect!!!
Thank you

 

[JHB]

You're welcome, good luck.