Multiply non-modifier scores by modifier score - Access SQL

Question

I have a table in MS Access which lists KPI (performance) scores for each person

USERID  KPI            SCORE  MODIFIER
20511   Productivity   50     False
20511   Cash per Hour  30     False
20511   Quality        0.9    True
21320   Productivity   60     False
21320   Cash per Hour  45     False

The non-modifier KPI scores are summed together and multiplied by the modifier score (any KPI where Modifier is marked true)

So in the example table above, user 20511 would have their Productivity and Cash per Hour scores summed to give 80 and this would be multiplied by the quality score of 0.9 to give 72.

An extra complication is that not all users have a modifier so I've been using a default modifier of 1 in this case.

So far I've come up with the below query

SELECT 
[userID], 
sum(IIF(Modifier = 0,([Score]),0)) AS summedScore, 
IIF(sum(iif(Modifier = 1,([Score]),0))=0,1,sum(iif(Modifier = 1,([Score]),0))) as modifierScore, 
summedScore * modifierScore as finalScore
FROM tbl_KPIScores
GROUP BY UserID

Which feels quite clumsy and also fails if the modifier score is actually 0. Is there a simpler way to do this?

is it possible to have several modifiers for the same employee, and if yes, what happens? — Thomas G, Feb 20 '17 at 10:47

score 1 · Accepted Answer · edited May 23 '17 at 11:53

Here's the solution for the easiest case, when you only have one modifier for each employee, or if there are several of them and you only want to pick up one randomly (I used FIRST() aggregate function)

SELECT
    T.userID, T.summedScore, Tmod.modif AS modifier, (T.summedScore *  NZ(Tmod.modif,1)) AS finalscore
FROM
(
    SELECT [userID], sum([score]) AS summedScore
    FROM tbl_KPIScores
    WHERE Modifier=False
    GROUP BY UserID
) AS T
LEFT JOIN
(
    SELECT userID, FIRST([score]) AS modif 
    FROM tbl_KPIScores
    WHERE Modifier=True
    GROUP BY UserID
) AS Tmod ON T.userID=Tmod.userID

If you have several modifiers, that becomes complicated. Because your modifier being a coefficient, it should thus be multiplied for each employee instead of being summed. And MS Access doesn't have a multiplying aggregate function. It can, however, be reproduced with some difficulties, using anti logarithm approach. I won't develop this here as you have not answered my comment about this. Check this question: Aggregate multiplicate function

Hi @Thomas G. Thanks for your answer. For now there is only 1 modifier per employee. I have tried to use your solution but am getting an error message saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing" ? — Leroy, Feb 20 '17 at 12:04
Ah, it was just the commas after the SELECT parts in the sub queries. I've edited the answer and it works great now. Thank you for your help! — Leroy, Feb 20 '17 at 12:09

Multiply non-modifier scores by modifier score - Access SQL

1 Answers1