Counting data across 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?

PS, luv this site and all you guys!

 

[JerryDennison]

When you use the count function it is only counting RECORDS that match whatever criteria you've set. If you're then counting records for more than one field grouped by another. Only those records without null values will be counted for each column for each grouped by row. Are you then trying to add these column values together?

 

[numbercruncher]

Thanks for responding Jerry. I am not actually setting any criteria. I think a better illustration may help. Imagine the following table :

Person ID Field1 Field2 Field3
a1 2 4 5
b2 1 3 5
c3 2 3 4

Now all I am trying to achieve is a simple count of responses by each person and field. Eg., the output I am looking for is as follows:

PersonID TypeofResponse Field1Count Field2Count
a1 1 1 0
a1 2 2 0
a1 3 0 0
a1 4 1 0
a1 5 0 1

I have developed a half way solution which is to use multiple crosstab queries which are fed into one main query (joined by PersonID).

This is restrictive because if the responses change I will need to manually pull through the new field responses from the relevant crosstab query into the main one. I appreciate the crosstab changes with responses but I still need to perform this manual exercise.

It's driving me a little insane, especially seen as Excel does the job in one go as a pivot table. I was even thinking about creating a function to put in artificial values for the "group by" part of my original query but I am not sure whether this is possible.

Thanks in advance for any support!