Click here to Skip to main content
15,798,200 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have a table like this in ms access:

empid score category
emp1      3       a
emp1      4       b
emp1      3       c
emp1      5       c
emp1      2       c

i want output as:

emp id a-score b-score avg-c-score
emp1     3        4       3.333

I have to left join another table output with this output.

What I have tried:

I have used pivot but I am not able to get the avg part. Also left join with other table with the pivot is giving error:

TRANSFORM first(score)
SELECT empid
FROM tbl_score
where category in('a', 'b')
GROUP BY empid
PIVOT category
Updated 7-Aug-16 11:53am

1 solution

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:
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][^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900