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