If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query to see what percentage of people scored a specific number
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. |
#2
|
|||
|
|||
Query to see what percentage of people scored a specific number
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 "John" wrote in message ... 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. |
#3
|
|||
|
|||
Query to see what percentage of people scored a specific number
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 John wrote: 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201004/1 |
Thread Tools | |
Display Modes | |
|
|