I have a table below that I need to count the instances that a particular group is in a particular gate status. I'm looking for an Access SQL statement to achieve this please. Your help is greatly appreciated!
| Group | Task | Status |
|---|---|---|
| Group1 | Task1 | Gate1 |
| Group1 | Task2 | Gate3 |
| Group1 | Task3 | Gate3 |
| Group1 | Task4 | Gate4 |
| Group1 | Task5 | Gate5 |
| Group1 | Task6 | Gate5 |
| Group1 | Task7 | Gate5 |
| Group2 | Task1 | Gate3 |
| Group2 | Task2 | Gate3 |
| Group2 | Task3 | Gate5 |
| Group3 | Task1 | Gate4 |
| Group3 | Task1 | Gate4 |
Need the query to show this:
| Group | Gate1 | Gate2 | Gate3 | Gate4 | Gate5 |
|---|---|---|---|---|---|
| Group1 | 1 | 0 | 2 | 1 | 3 |
| Group2 | 0 | 0 | 2 | 0 | 1 |
| Group3 | 0 | 0 | 0 | 2 | 0 |
To include non-exist items in column-headings (like Gate2 here), specify them in the PIVOT clause:
TRANSFORM NZ(COUNT([Task]),0) AS N
SELECT [Group]
FROM [table name]
GROUP BY [Group]
PIVOT [Status] IN ("Gate1","Gate2","Gate3","Gate4","Gate5")