Counting data from multiple fields

[numbercruncher]

Hi folks

I am having difficulty in counting the contents of multiple fields using queries. I have used the group by and count tools in the query design view but this is only good for single fields.

For example, you need to select a field to group by therefore if data in another field is not present within the first, the counting process does not work.

I have tried using a cross tab query by trying to emulate a pivot table but this again is only good for the contents of one field and does cater for different values in multiple fields.

Can any one out there help with this most frustrating problem?

 

[fmbpcsup]

If I understand correctly, if you group by one field and count by another field then the counted field counts only the non-null entries? If that is the case, can you use a field that always has data to count by such as an ID field or a "required" field?

 

[numbercruncher]

Thanks for your comments fmbpcsup.

I am not concerned with null entries particularly. I am trying to summarise information across all the potential types of answers. For example a sample output would look something like what follows:

PersonID OutcomeType Field1Count Field2Count
A100 1 1 0
A100 2 0 3
A100 3 2 1
B100 1 1 0
B100 2 0 0
B100 3 1 1

So you can see for each person in a simple table I am trying to count the number of times they have a certain outcome as identified in the second column above.

Any ideas anybody?

Thanks

 

[fmbpcsup]

I'm not sure of the purpose of the two count columns, field1count and field2count. Do they represent the number of times a person has answered a particular type on different occasions? Can these two fields be added? It would make a crosstab query easier to produce.