CODEWITHSUNDEEP
sqlms-access
Shaves
Shaves

Reputation: 930

Access 2007 query to count different values in the same column

I'm using Access 2007. In column1 are the values (Week 1, Week 2, Week 3) and in column2 are the cost centers (01, 03, 05, 12, 24, etc.). I'm trying to create a query that will count all instances of column1 for each cost center and display the results in 3 different columns. For example:

Cost Center      Week1     Week2    Week3
01             15       25        3
03              8        10       12
05             200       11       55

Doesn't seem like it should be too hard but I can't get it to work. Here is the code that works only if I count a particular week

SELECT Mid([TransTable]![LGCY_IORG_ID],1,2) AS Expr1, 
       Count(TransTable.Week) AS CountOfWeek
FROM TransTable
WHERE (((TransTable.Week)="Week 1"))
GROUP BY Mid([TransTable]![LGCY_IORG_ID],1,2);

I'd like to get Week 1 in one column, Week 2 in another column, and week 3 in a 3rd column, thanks for the help.

Upvotes: 0

Views: 227

Answers (2)

Abdullah Dibas
Abdullah Dibas

Reputation: 1507

SELECT column2 As CostCenter, 
IIF(column1 = 'Week 1' , column2 , 0) As Week1,
IIF(column1 = 'Week 2' , column2 , 0) As Week2,
IFF(column1 = 'Week 3' , column2 , 0) As Week3 
FROM TransTable
GROUP BY column2

Upvotes: 1

John Mo
John Mo

Reputation: 1326

Use the Crosstab Query Wizard.

Row headings will be the cost center column. Column headings are the column with the Week descriptions. Values will be Count of Week.

Upvotes: 1

Related Questions