Counting by criteria (i think!)?

[Lindsey M]

Hi everyone, hope you can help on this as its drving me crazy!!

I have a table that has the following info in it:

Fields:
Service Band Number
Benefits EE 31
Benefits EP 63
Benefits ME 24
Benefits SDN 8
Benefits UNA 28
Academy EE 31
Academy EP 42
Academy ME 9
Academy SDN 12
Academy UNA 2
Revenues EE 13
Revenues EP 11
Revenues ME 21
Benefits EE 2
Benefits EP 43
Benefits ME 75
Benefits SDN 3
Benefits UNA 18
etc etc

There are 6 Services in total and 5 Bands. What I want to do is create a
Crosstab query that will just have the Service as the row and the Band as
the column and then will add up all the numbers - does that make sense? So
it would look something like this?

Service EE EP ME SDN UNA
Benefits 121 56 54 99 12
Academy 23 56 23 12 2
Revenues 12 45 56 76 4
Environmental 4 34 75 111 16
Recruitment 23 56 89 35 29
Blended 34 32 12 65 21

Has anyone got any ideas on how to do this as everything I am trying just
doesn't work??

Cheers in advance, and a speedy response would be very much appreciated! )

Linds

 

[Joan Wild]

There are 6 Services in total and 5 Bands. What I want to do is
create a Crosstab query that will just have the Service as the row
and the Band as the column and then will add up all the numbers -
does that make sense? So it would look something like this?

Service EE EP ME SDN UNA
Benefits 121 56 54 99
12 Academy 23 56 23 12
2 Revenues 12 45 56 76
4 Environmental 4 34 75 111 16
Recruitment 23 56 89 35 29
Blended 34 32 12 65
21

TRANSFORM Sum(Table1.[Number]) AS [SumOfNumber]
SELECT Table1.[Service], Sum(Table1.[Number]) AS [Total Of Number]
FROM Table1
GROUP BY Table1.[Service]
PIVOT Table1.[Band];

Start a new query and dismiss the choose table dialog. Go to SQL view and
paste the above in. You'll need to substitute the name of your table for
Table1.