By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,723 Members | 1,304 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,723 IT Pros & Developers. It's quick & easy.

Calculate percentage on report from TEXT fields?

ollyb303
P: 74
Hello,

Trying to help a friend/colleague out with a database and we've both drawn a blank. Not even sure if this is possible.

The database has a table (Table1) with a several columns: ID, QuestionA, QuestionB, QuestionC, etc.

Each of these is populated from a form (Form1) which uses combo boxes with values of YES, NO and NA.

He has created a report which displays the answers to the questions for each record, but wants to calculate the percentage of records where the answer to each question is YES (discounting any where the answer is NA).

i.e. (and I apologise for this format!) (Count of records where QuestionA = "YES") / (Count of records where QuestionA = "YES" or "NO") as a percentage.

Any ideas?

Many thanks
Jan 17 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 374
In order to be able to do that, you would need to enter into a textbox control in it's control source the following line:
Expand|Select|Wrap|Line Numbers
  1. =FORMATPERCENT(DCOUNT("[ID]","[TABLE1]","[YesNoFieldInTable] = True")/TotalNumberOfQuestions,2)
  2.  
The ID field is the Primary Key in the table
Table1 would be the name of the table
YesNoFieldInTable is the name of the field that holds the yes/no or True/False answer
TotalNumberOfQuestions is the number of questions that you're asking.

If the field that holds the answer physicaly has text instead of Logical statement then you would enter the following
Expand|Select|Wrap|Line Numbers
  1. =FormatPercent(DCOUNT("[ID]","[TABLE1]","[YesNoTextFieldinTable] = 'Yes'")/TotalNumberOfQuestions,2)
  2.  
Jan 17 '08 #2

ollyb303
P: 74
In order to be able to do that, you would need to enter into a textbox control in it's control source the following line:
Expand|Select|Wrap|Line Numbers
  1. =FORMATPERCENT(DCOUNT("[ID]","[TABLE1]","[YesNoFieldInTable] = True")/TotalNumberOfQuestions,2)
  2.  
The ID field is the Primary Key in the table
Table1 would be the name of the table
YesNoFieldInTable is the name of the field that holds the yes/no or True/False answer
TotalNumberOfQuestions is the number of questions that you're asking.

If the field that holds the answer physicaly has text instead of Logical statement then you would enter the following
Expand|Select|Wrap|Line Numbers
  1. =FormatPercent(DCOUNT("[ID]","[TABLE1]","[YesNoTextFieldinTable] = 'Yes'")/TotalNumberOfQuestions,2)
  2.  
Thanks for the reply,

I'm not sure I explained myself properly

We're not trying to calculate the total percentage of questions which were answered "yes" - For each question we need to calculate the percentage of records on which that question was answered "yes" - so the total number of questions is irrelevant.

We would need to add a different textbox for each question which displays total "yes"/total "yes" or "no" for that question only.

Hope that's clearer.

Many thanks
Jan 17 '08 #3

missinglinq
Expert 2.5K+
P: 3,532
Wouldn't you need to divide the total Yeses for the question divided by the total number of records?

Maybe

Expand|Select|Wrap|Line Numbers
  1. =FORMATPERCENT(DCOUNT("[ID]","[TABLE1]","[YesNoFieldInTable] = True")/DCOUNT("[ID]","[TABLE1]"))
  2.  
Linq ;0)>
Jan 17 '08 #4

ollyb303
P: 74
Seems odd perhaps, but no. We want to exclude any "NA" answers from the equation completely. Have successfully solved it now with the following:

Expand|Select|Wrap|Line Numbers
  1. =FormatPercent(DCount("[ID]","[Table1]","[QuestionA] = 'yes'")/(DCount("[ID]","[Table1]","[QuestionA]='yes'")+DCount("[ID]","[Table1]","[QuestionA]='no'")))
Many thanks both of you.
Jan 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.