Help with the SQL

[Guest]

I am trying to generate the following information and I could really use some
help with it.

I have a Qry called [Qry_Pt Satisfaction Points Counter] and it contains
several fields [B2] [b3] [c2] etc 20 in total. each field is either Null or
contains a number 1-5. I need to know how many 1's,2's,3's,4's,5's there are
for each field. Here is how I hope I can get the final result to look.

1s 2s 3s 4s 5s
_______________________________________________
B2 0 4 6 2 10
b3 2 4 12 45 21

I hope this makes sense

thank you Jason

 

[Guest]

As is usualy the case with me I figured out a solutaion right after I decided
to post my question. Here it is
thank you

SELECT "B2" As Source,Abs(sum([b2]=5)) as Five ,Abs(sum([b2]=4)) as Four,
Abs(sum([b2]=3)) as Three,Abs(sum([b2]=2)) as two, Abs(sum([b2]=1)) as One
From [Qry_Pt Satisfaction Points Counter]
Union SELECT "B3" As Source,Abs(sum([b3]=5)) as Five ,Abs(sum([b3]=4)) as
Four, Abs(sum([b3]=3)) as Three,Abs(sum([b3]=2)) as two, Abs(sum([b3]=1)) as
One From [Qry_Pt Satisfaction Points Counter];

 

[Guest]

Please post example of the input data.

 

[Michel Walsh]

Normalize your table with a query like:


SELECT b2 AS point, "b2" AS category FROM myTable
UNION ALL
SELECT b3, "b3" FROM myTable
UNION ALL
SELECT c2, "c2" FROM myTable
UNION ALL
SELECT c3, "c3" FROM myTable
UNION ALL
....



Save it, say under the name qu1, or make a table out of it (in that case,
index each field)..


Next, write the following crosstab query:

TRANSFORM Nz(COUNT(*),0) AS theCell
SELECT category
FROM qu1
GROUP BY category
PIVOT point & "s"




Hoping it may help,
Vanderghast, Access MVP