Counting occurrences with SELECT DISTINCT, SELECT COUNT and UPDATE in Access

Question

In Access, I have a table like this one:

Date      | EmployeeNum | Award
11-JAN-08 | 34          | GoldStar
13-JAN-08 | 875         | BronzeTrophy
13-JAN-08 | 34          | BronzeTrophy
18-JAN-08 | 875         | BronzeTrophy

And I want to have a table count them like this:

EmployeeNum | GoldStar | BronzeTrophy
34          |    1     |      1
875         |    0     |      2

I want to be able to generate this table by running a query or something similar. I've tried putting this into a query but I'm not really sure I'm doing it right. I've tried using UPDATE and SET = SELECT COUNT without too much success.

How should I do this? SHOULD I be trying it like that?

Answer 1

You will need a crosstab (aka pivot) query for that. Try the following SQL and modify to suit your needs:

TRANSFORM Count(MyTable.EmployeeNum) AS AantalVanEmployeeNum
SELECT MyTable.EmployeeNum
FROM MyTable
GROUP BY MyTable.EmployeeNum
PIVOT MyTable.Award;

Answer 2

In Access, try "/View/Pivot Table View".

Answer 3

I would use the query below to get your totals:

SELECT EmployeeNum, SUM(Case [Award] WHEN 'GoldStar' THEN 1 ELSE 0 END) As [GoldStar], SUM(CASE [Award] WHEN 'BronzeTrophy' THEN 1 ELSE 0 END) As [BronzeTrophy] FROM MyTable Group By EmployeeNum

Answer 4

birger, I like that, but how do I update a table with that code? Another thing I'd like to do is do it WHILE Date >= #13-JAN-08#. How do I implement this? I can't seem to add it.

tjdowns, this is only an example. In reality there are 42 "kinds of awards". In reality, I wanted my final table to look more like this:

EmployeeNum | Stars |  Trophies
34          |    1  |      1
875         |    0  |      2

In the end, I will need to add up the Gold, Silver, Puce, and Platinum Stars into a sum and store THAT instead of 'how many Bronze Stars'. I'll also need to track "in the last 60 days" and such because they factor into a big formula that gives each employee a rank. I figured I'd start small for now, ya know?

Thank for your help, guys!! I really appreciate it.