By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 1,942 Online
Bytes IT Community
+ 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

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

Share this Question
Share on Google+
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
  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;
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
  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
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

NeoPa
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
  1. SELECT Count(*) AS [Total]
  2.      , Sum(IIf(StatusLevel>=4,1,0)/Total AS [FourOrMore]
  3. 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
File Type: zip 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

Post your reply

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