Count function to summarize multiple fields in one query

[RCamper]

Hello,



Here is an example of my data:

Field1	Field2	Field3
Certain	Certain	Possible
Probable	Unsure	Certain
Certain	Possible	Probable

I want to summarize it to look like this:

Certainty	Field1	Field2	Field3
Certain	2	1	1
Probable	1	0	1
Possible	0	1	1
Unsure	0	1	0

In other words, I would like to get a count of each text occurrence for each field.

Thanks in advance.

[June7]

Data structure is not normalized. If it were, could use a CROSSTAB query.

How many 'certainty' values are there - only those 4? Try a UNION query. No designer or builder for UNION, must type (or copy/paste) into SQL View window of query designer.

SELECT "Certain" As Certainty, Sum(IIf(Field1="Certain",1,0)) As F1, Sum(IIf(Field2="Certain",1,0)) As F2, Sum(IIf(Field3="Certain",1,0) )As F3 FROM tablename
UNION SELECT "Probable", Sum(IIf(Field1="Probable",1,0)), Sum(IIf(Field2="Probable",1,0)), Sum(IIf(Field3="Probable",1,0)) FROM tablename
UNION SELECT "Possible", Sum(IIf(Field1="Possible",1,0)), Sum(IIf(Field2="Possible",1,0)), Sum(IIf(Field3="Possible",1,0)) FROM tablename
UNION SELECT "Unsure", Sum(IIf(Field1="Unsure",1,0)), Sum(IIf(Field2="Unsure",1,0)), Sum(IIf(Field3="Unsure",1,0)) FROM tablename;