Report Percentage Calculation - HELP

[TheBlade]

I have a report with 30 fields per record (each has a numeric value from 1-5). What I want to do is calculate the total percentage of record fields that are <3. Anybody have an idea on the best way to do this in the report? it is difficult to do in a query, cos I'd have to specify criteria for each field and there isn't enough room to do this for all of them.

Thanks in advance!

TheBlade

 

[tomschafer]

I would create a cross-tab query based off the table- arranging the columns into rows. Then query the cross tab as the data source for the report.

 

[TheBlade]

Hi there. I am not familiar with crosstab queries. Can you please be a bit more specific?

thanks!

 

[tomschafer]

Please forgive me I read your post a little fast- I thought you were converting rows into columns- that's what a crosstab query does. If you ever get curious there is a wizard for it when you click the new button to make a new query. As for converting columns into rows (which is what I was thinking of) I don't know of an easy way to do it. How many rows are you returning with the query now? And what kind of criteria are you using?

 

[TheBlade]

Hi Pezamystik.

I have solved the problem now, although it was a little long winded. I created a query that did a dcount statement for each field to extract the number of <3 records for each field. A 2nd query added them all up and divided them by the total # of records X total # of fields.

cheers

TheBlade