Clever Geek Handbook

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

In Access, I have a table like this:

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 the table to count them like this:

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

I want to create this table by running a query or something similar. I tried to include this in the request, but I'm not sure if I'm doing it right. I've tried using UPDATE and SET = SELECT COUNT without much success.

How should I do it? SHOULD I try this?

+1


source to share


3 answers


To do this, you will need a crosstab query (aka pivot). Try using the following SQL and change to suit your needs:



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


source


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



0


source


I would use the following query 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
-1


source






More articles:


All Articles