Aggregate IIF in access (1 Viewer)

[Portucale]

Hi,

I am trying to aggregate IIF functions to give me the total in separate columns (fields) according to the criteria applied however I am getting an error message "You tried to execute a query that does not include the specific expression 'IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0)' as part of an aggregate function, and I cannot find why, any help is very much appreciated.

The query is as follows:

SELECT

Tbl_Advisor_raw.Month,
Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay,
Sum(Tbl_Advisor_raw.Salary) AS Salary,
Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue,
IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend,


IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp1,
IIF(Tbl_SKG.Value_Grp = 2,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp2,
IIF(Tbl_SKG.Value_Grp = 3,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp3

FROM (Tbl_Advisor_raw INNER JOIN (Tbl_EmployeeAll INNER JOIN Tbl_Centres ON Tbl_EmployeeAll.Location = Tbl_Centres.OrgLocation) ON Tbl_Advisor_raw.Payroll = Tbl_EmployeeAll.EmployeeID) INNER JOIN Tbl_SKG ON Tbl_Advisor_raw.SKG = Tbl_SKG.SkillGroup

GROUP BY Tbl_Advisor_raw.Month;

Thanks,

 

[pr2-eugin]

Portucale, Make sure that the Calculated fields form a part of the Totals.. In the Query Grid select 'Expression', just like you selected Sum/Group By..

 

[Portucale]

Hi,

Yes I have the totals marked as "Expression" :banghead:

Not sure how to insert a jpg to show.

Thanks again for teh help,

 

[pr2-eugin]

So did you try Group By?

Check the thread.. If you need to post a picture or database before 10 posts...

 

[Portucale]

Hi,

Yes I think the issue is in the Group By, not sure what is missing, I've tried to enter:
ValueGrp1, ValueGrp2, ValueGrp3;

But doesn't work either, so really not sure what I am missing

Thanks,

 

[pr2-eugin]

Okay.. Just copy & paste the following..

SELECT Tbl_Advisor_raw.Month, Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay, Sum(Tbl_Advisor_raw.Salary) AS Salary, Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue,
IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend, IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp1, IIF(Tbl_SKG.Value_Grp = 2,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp2, IIF(Tbl_SKG.Value_Grp = 3,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp3 
FROM (Tbl_Advisor_raw INNER JOIN (Tbl_EmployeeAll INNER JOIN Tbl_Centres ON Tbl_EmployeeAll.Location = Tbl_Centres.OrgLocation) ON Tbl_Advisor_raw.Payroll = Tbl_EmployeeAll.EmployeeID) INNER JOIN Tbl_SKG ON Tbl_Advisor_raw.SKG = Tbl_SKG.SkillGroup 
GROUP BY Tbl_Advisor_raw.Month, IIf(ValuePay>0,(ValuePay/Salary),0), IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0), IIF(Tbl_SKG.Value_Grp = 2,sum(Tbl_Advisor_raw.ValuePay),0), IIF(Tbl_SKG.Value_Grp = 3,sum(Tbl_Advisor_raw.ValuePay),0);

 

[Portucale]

Hi Paul,
Getting message "Cannot have aggregate function in GROUP BY clause (IIF(Tbl_SKG.Value_Grp = 1,sum(Tbl_Advisor_raw.ValuePay),0).

Any idea?

 

[pr2-eugin]

Lets try this the other way then.. Perform the Sum if the value is 1,2,3..

SELECT Tbl_Advisor_raw.Month, Sum(Tbl_Advisor_raw.ValuePay) AS ValuePay, Sum(Tbl_Advisor_raw.Salary) AS Salary, Sum(Tbl_Advisor_raw.NetRevenue) AS NetRevenue,
IIf(ValuePay>0,(ValuePay/Salary),0) AS pcSpend, Sum(IIF(Tbl_SKG.Value_Grp = 1,Tbl_Advisor_raw.ValuePay,0)) as ValueGrp1,
Sum(IIF(Tbl_SKG.Value_Grp = 2,Tbl_Advisor_raw.ValuePay,0)) as ValueGrp2,
Sum(IIF(Tbl_SKG.Value_Grp = 3,Tbl_Advisor_raw.ValuePay,0)) as ValueGrp3
FROM (Tbl_Advisor_raw INNER JOIN (Tbl_EmployeeAll INNER JOIN Tbl_Centres ON Tbl_EmployeeAll.Location = Tbl_Centres.OrgLocation) ON Tbl_Advisor_raw.Payroll = Tbl_EmployeeAll.EmployeeID) INNER JOIN Tbl_SKG ON Tbl_Advisor_raw.SKG = Tbl_SKG.SkillGroup
GROUP BY Tbl_Advisor_raw.Month;

Copy and paste.. Let me know what happens..

 

[Portucale]

Hi Paul,

I am sure that I had tried something similar but for some reason didn't work,

MANY THANKS the query is now doing what supposed to do.

Cheers,

 

[pr2-eugin]

Glad to have helped.. Good Luck..