434,894 Members | 1,942 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

# Get a percentage for number of records with a value of 4 or higher

 P: 5 Hi, I have an access table to record my evaluation/feedback data from my teaching at the Museum I work at. I have the table and form set up, but am stumbling on the report. My boss wants me to report on the percentage of people who answered the overall satisfaction as 4 or 5 (there is a 1-5 option). How do I get the percentage of people/entries who rated it 4 or 5. I have set the form up as a drop down where 1,2,3,4 or 5 can be selected. I almost need an experssion which can work out (number of overall statisfaction entries >3) / (total number of overall satifisfaction entries) * 100 You can tell I'm not a computer tech. I'm not sure if this sould be done in a query or report. Any help/advice would be much appreciated. Jul 26 '12 #1

#### ✓ answered by john garvey

Hi Anna

In MS Access (whatever version you have) Create a table called 'tblPeople', within that table create two columns(fields) the first data type 'text'; field size '50'; name 'person' make this the primary Key
the second data type 'Number' size 'long'; name 'StatusLevel'
Save the table as tblPeople

Open the table and type in about 10 to 15 names in the person field and scores in the StatusLevel field between 1 and 5.
Open a blank query, close the choose table window without choosing anything, in the top left hand corner of the ribbon (Menu) it should say SQL. Click this and the query will open in SQL mode with 'SELECT;' highlighted. Copy the query below and paste it into the query grid which should delete the 'SELECT;' text that was there previously
Expand|Select|Wrap|Line Numbers
1. SELECT Count(StatusLevel) AS Total,
2. Format(Count(IIf(StatusLevel< 4,0))/Total, "percent") AS LessThanFour,
3. Format(Count(IIf(StatusLevel=4,0))/Total , "percent") AS Four,
4. Format(Count(IIf(StatusLevel=5,0))/Total , "percent") AS Five
5. FROM tblPeople;
To run the query first save it as say 'qryStatus' and then double click it to open and the % data should be there.

Kind regards

7 Replies

 P: 50 Hi AnnaNMuseum Assuming your table is called 'tblPeople', and your result field is called 'StatusLevel' you can extract the percentages with this query. Base your report on a query or queries Expand|Select|Wrap|Line Numbers SELECT Count(StatusLevel) AS Total,  Format(Count(IIf(StatusLevel< 4,0))/Total, "percent") AS LessThanFour,  Format(Count(IIf(StatusLevel=4,0))/Total , "percent") AS Four,  Format(Count(IIf(StatusLevel=5,0))/Total , "percent") AS Five  FROM tblPeople; Kind regards Jul 26 '12 #2

 P: 5 Thank you for replying john garvey. I think this might be a bit above my access level ;) I tried to input the code you supplied in a query as criteria, but it came back at me saying i needed to put parenthesis on the sub query. I'm not sure I even put the code in the right place. But thank you for your help though. Jul 26 '12 #3

 P: 50 Hi Anna Please try this; In MS Access (whatever version you have) Create a table called 'tblPeople', within that table create two columns(fields) the first data type 'text'; field size '50'; name 'person' make this the primary Key the second data type 'Number' size 'long'; name 'StatusLevel' Save the table as tblPeople Open the table and type in about 10 to 15 names in the person field and scores in the StatusLevel field between 1 and 5. Open a blank query, close the choose table window without choosing anything, in the top left hand corner of the ribbon (Menu) it should say SQL. Click this and the query will open in SQL mode with 'SELECT;' highlighted. Copy the query below and paste it into the query grid which should delete the 'SELECT;' text that was there previously Expand|Select|Wrap|Line Numbers SELECT Count(StatusLevel) AS Total,  Format(Count(IIf(StatusLevel< 4,0))/Total, "percent") AS LessThanFour,  Format(Count(IIf(StatusLevel=4,0))/Total , "percent") AS Four,  Format(Count(IIf(StatusLevel=5,0))/Total , "percent") AS Five  FROM tblPeople; To run the query first save it as say 'qryStatus' and then double click it to open and the % data should be there. Kind regards Jul 26 '12 #4

 P: 5 Hi John, Thank you so much for your clear instructions, I am so close. I did as you instructed, but when I open the query at the last step, a box titled "Enter Parameter Value" pops up and prompts me to to enter a StatusLevel value. When I enter 4 for example it reads all enteries in the table as fours (i ranked all the way from 1-5 in the table)and gives a result that 4 is 100% and the other two catagories scored nothing. Any tips on how to avoid this? Jul 27 '12 #5

 Expert Mod 15k+ P: 31,491 If you'rew getting that prompt Anna, it means you haven't set the table up as instructed. Your SQL can probably be a little simpler to match the question, but nothing will work as expected unless the table is created according to John's instructions ;-) Expand|Select|Wrap|Line Numbers SELECT Count(*) AS [Total]      , Sum(IIf(StatusLevel>=4,1,0)/Total AS [FourOrMore] FROM [tblPeople] Jul 27 '12 #6

P: 50
Hi Anna

Unzip the attached files and try again, they are access 2007.

Kind regards
Attached Files
 percentTest.zip (16.5 KB, 89 views)
Jul 27 '12 #7

 P: 5 John, you are an absolute Star!! I just tried the formula on the original table, just switching out the titles, and it worked perfectly! thank you so much. Just going to do a new post to ask how to run it with the data sorted by month. Thank you a million Jul 29 '12 #8