459,723 Members | 1,304 Online
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?

 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
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 =FORMATPERCENT(DCOUNT("[ID]","[TABLE1]","[YesNoFieldInTable] = True")/TotalNumberOfQuestions,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 =FormatPercent(DCOUNT("[ID]","[TABLE1]","[YesNoTextFieldinTable] = 'Yes'")/TotalNumberOfQuestions,2)   Jan 17 '08 #2

 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 =FORMATPERCENT(DCOUNT("[ID]","[TABLE1]","[YesNoFieldInTable] = True")/TotalNumberOfQuestions,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 =FormatPercent(DCOUNT("[ID]","[TABLE1]","[YesNoTextFieldinTable] = 'Yes'")/TotalNumberOfQuestions,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

 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 =FORMATPERCENT(DCOUNT("[ID]","[TABLE1]","[YesNoFieldInTable] = True")/DCOUNT("[ID]","[TABLE1]"))   Linq ;0)> Jan 17 '08 #4

 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 =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