count filled fields in crosstab report

[jjenn7]

how do I count the number of fields of a record that hold a value in a
crosstab report?

Ex: col1 col2 col3 col4
12 15 Count = 2

col1 col2 col3 col4
12 8 15 Count = 3


I don't know how to use the count method where each field name in the
crosstab report is different.

Thanks for any help.

 

[Duane Hookom]

You should be able to set up another Row Heading in your crosstab.
Field: YourField
Total: Count
Crosstab: Row Heading

 

[jjenn7]

Thanks for the response. I tried that, but it doesn't give me the number I
need because 1) one of the report fields is an average of two of the crosstab
query fields thus the crosstab count counts too many fields and 2) I never
want to count one specific field even if it has a value: e.g., I need to add
Col5 value to the sum of fields (columns 1-5), but average by number of
filled fields minus Col5 (columns 1-4). The crosstab field is [Grade], but
in the crosstab report I can't call [Grade] and I don't know how to or if
it's possible to call the specific [Grade] columns.

 

[Duane Hookom]

You are losing me with your requirements and no sql and no example. Assuming
you want to count how many columns have values as per your first question.
Take your crosstab query and change it back to a standard totals query.
Remove the field/column that was the value. Save this query as
"qtotWasCrosstab". Then add this query to your crosstab joining all the row
heading fields. Use Count as a row heading on the column that had been your
Column Heading. This should count the unique values from the column
headings. If you want to ignore specific values, set a criteria in your
"qtotWasCrosstab".

--
Duane Hookom
MS Access MVP

Thanks for the response. I tried that, but it doesn't give me the number I
need because 1) one of the report fields is an average of two of the crosstab
query fields thus the crosstab count counts too many fields and 2) I never
want to count one specific field even if it has a value: e.g., I need to add
Col5 value to the sum of fields (columns 1-5), but average by number of
filled fields minus Col5 (columns 1-4). The crosstab field is [Grade], but
in the crosstab report I can't call [Grade] and I don't know how to or if
it's possible to call the specific [Grade] columns.

You should be able to set up another Row Heading in your crosstab.
Field: YourField
Total: Count
Crosstab: Row Heading