Excel Pivot Table Question

[Terpsfan]

I have created a database in Access and am trying to do a
report that displays as a crosstab. I want to show the
compliance % per division and then region on the column
level and the question number on the row level. However
Access crosstabs are limited to one column deep for a
grouping level. So then I thought about using an Excel
Pivot Table to display this data.

I succeed in getting a simple count of a field in the
value area for all values with the question number as the
row; and division and region as the columns; and sample
month as the page value. I can't seem to get the right
expression to calculate the percentage of values where a
value is true divided by the total number of values.

The field names are: ReviewID, QNumber, Answer, Region, Division
.

 

[SkipVought]

Hi Omega,

You have an issue where you are finding an average of a count of a specific value within the count of values. Excel Pivot Tables does do a limited % within, but not like that. You will have to either manipulate two pivot tables, the second based on the first, where the first will give you the total count and the second will five you the count of TRUE for each question.

I played around with it and I do not see any other way. However, I am still "mulling it over"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Terpsfan]

Thanks for your reply. I will try this one out and see where it goes. I have two other options I will explore, using Crystal Reports and OLAP cubes. The problem is that this kind of report I'm trying to do isn't really covered well in any books I've seen.

 

[Terpsfan]

The solution involved making the answer field a boolean field, in which a yes = 1 and a no = 0. Then in my computed field I used an average of the answer field to get the percentage I needed. I used this same solution for Crystal Reports, so it looks like I answered my own question.