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

[AnnaNMuseum]

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.

[john garvey]

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

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

[AnnaNMuseum]

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.

[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

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

[AnnaNMuseum]

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?

[NeoPa]

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

SELECT Count(*) AS [Total]
     , Sum(IIf(StatusLevel>=4,1,0)/Total AS [FourOrMore]
FROM [tblPeople]

[john garvey]

Hi Anna

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

Kind regards

[AnnaNMuseum]

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