Counting by criteria (i think!)?

  • Thread starter Thread starter Lindsey M
  • Start date Start date
L

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! :o)

Linds
 
Lindsey said:
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
Click to expand...

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.
 
Joan,

Thank you so very much, that works a treat, and taught me a little about SQL
at the same time - cool!!

Cheers again
Linds :o)

Joan Wild said:
Lindsey said:
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
Click to expand...

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.
Click to expand...
 
Back
Top