Left join? Where? I do not see....
Besides, you're on the right truck. You have to replace
FIRST
with
AVG
. That's all.
Try this:
TRANSFORM AVG(t.score)
SELECT t.empid
FROM tbl_score AS t
GROUP BY t.empid
PIVOT t.category
Note, that you cannot pivot data on multiple fields... So, it's impossible to get
FIRST((a, b))
and
AVG(c)
in one query.
For further details, please see:
TRANSFORM Statement (Microsoft Access SQL) [Access 2007 Developer Reference][
^]