Query to see what percentage of people scored a specific number

[John]

I am fairly new to access and have created a great database. However the
queries are now becomming more complicated than I expected. I have two fields
(Goal One and Goal Two) the nurse asks the patient have they met their goal.
They use a 1-5 rating scale. 1=no, 2=sometimes, 3=likely, 4=most and
5=always. They select the number from a drop down list. I need to see how
many patients were given a one, a two, a three etc.... and what percentage of
patients were given a three or higher. I need to know this for goal one and
goal two seperatly.

 

[vanderghast]

There are many ways. A relatively simple on is to have a table, Iotas, one
field, itoa, the primary key, with 5 records, with values 1, 2, 3, 4, 5.
Once you have that table, make a new query,

bring the original table,
bring table iotas
change the query to a total query
bring the iota field in the grid,
under it, keep the proposed GROUP BY
bring the field of the first goal in the grid,
under it, change its GROUP BY to WHERE
and in the criteria line, have: >= [iotas].[iota]
bring the field of the first goal in the grid, a second time.
under it, change its GROUP BY to COUNT


That query returns the number of record having at least 1, at least 2, at
least 3, ... etc, for the first question.

To have a percentage instead of an absolute count, edit the statement in SQL
view, change the

COUNT(firstGoalFieldNameHere)

to

COUNT(firstGoalFieldNameHere) / (SELECT
COUNT(firstGoalFieldNameHere) FROM tableNameHere)


Have a second query for the second question. You could have done it in just
one query, but with a different design of the original table:

PatientID, GoalNumber, Evaluation
1010 1 3
1010 2 5


instead of the actual design:

PatientID Goal1 Goal2
1010 3 5


In fact, the proposed design would very simply allow more than two goals)





Vanderghast, Access MVP

 

[KenSheridan via AccessMonster.com]

Another way, which would return the various totals as columns of a single row
rather than as separate rows, would be to sum the return value of an
expression which returns one or zero depending on the value entered as the
achievement for the goal. Summing the ones is the same as counting the rows
with the relevant value.

For the percentages its just a case of doing this for values of 3 or more,
dividing this by the count of all rows and multiplying by 100. So the query
would be like this:

SELECT
SUM(IIF([Goal One] = 1,1,0)) AS [Goal One:1],
SUM(IIF([Goal One] = 2,1,0)) AS [Goal One:2],
SUM(IIF([Goal One] = 3,1,0)) AS [Goal One:3],
SUM(IIF([Goal One] = 4,1,0)) AS [Goal One:4],
SUM(IIF([Goal One] = 5,1,0)) AS [Goal One:5],
SUM(IIF([Goal One] >= 3,1,0))/COUNT(*)*100 AS [Goal One:3Plus Percent],
SUM(IIF([Goal Two] = 1,1,0)) AS [Goal Two:1],
SUM(IIF([Goal Two] = 2,1,0)) AS [Goal Two:2],
SUM(IIF([Goal Two] = 3,1,0)) AS [Goal Two:3],
SUM(IIF([Goal Two] = 4,1,0)) AS [Goal Two:4],
SUM(IIF([Goal Two] = 5,1,0)) AS [Goal Two:5],
SUM(IIF([Goal Two] >= 3,1,0))/COUNT(*)*100 AS [Goal Two:3Plus Percent]
FROM [Patients];

You can of course change the column names to represent the text of each level
of achievement rather than the number if you wish, e.g. instead of [Goal One:
2] use [Goal One: Sometimes] and so on.

You could then create a simple form or report based on the query for better
presentation of the results.

Ken Sheridan
Stafford, England