locked
Aggregate IIF in access RRS feed

  • Question

  • User-965657988 posted

    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,

    Monday, March 18, 2013 8:12 AM

Answers

  • User1938476581 posted

    Hi,

    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

    I think you should changing "=" to "==" like this

    IIF(Tbl_SKG.Value_Grp == 1,sum(Tbl_Advisor_raw.ValuePay),0) as ValueGrp1

    Hope it can help you.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 18, 2013 11:31 PM