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.
0
Utf
4/26/2010 1:55:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
608 Views

Similar Articles

[PageSpeed] 38

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" <John@discussions.microsoft.com> wrote in message 
news:FB912708-597F-4938-9B8E-A581F77A74C5@microsoft.com...
>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. 

0
vanderghast
4/26/2010 2:44:40 PM
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/Forums.aspx/access-queries/201004/1

0
KenSheridan
4/26/2010 4:10:30 PM
Reply:

Similar Artilces:

Page Numbers in Publisher 2000
I am completing our Employee Handbook and do not want page umbers on my first 9 pages. Page 10 is the first one I want numbered beginning with page #1. Instead my page #10 is labeled page #1. Can anyone help and let me know if this is possible with this software? Thanks. -- Kay -- Kay Correction "Kay" wrote: > I am completing our Employee Handbook and do not want page numbers on my first 9 pages. Page 10 is the first one I want numbered beginning with page #1. > Instead my page #10 is labeled page #10. Can anyone help and let me know if > this is possibl...

sequential invoice numbering
How do I formulate a cell to add one to the previous invoice# each time the "xxxx" invoice file is opened. Please and thank you Scott You could add code to the workbook_open event, thus (to add this open the VBE (Alt+F11) and right click on the ThisWorkbook entry for the file name. Paste the code in the resultant window, close the VBE and save the workbook. (Change the references accordingly) Private Sub Workbook_Open() Worksheets("Sheet1").Range("A1").Value = Worksheets("Sheet1").Range("A1").Value + 1 End Sub -- HTH Nick Hodge Microso...

two columns range of numbers need to list all numbers in the range
have two columns range of numbers i need to list each number in the range start end 5 9 15 19 20 29 i need for each row to show the numbers in the range 5 6 7 8 9 15 16 .. .. pls help Was this post helpful to you? Check your other post. arsovat wrote: > > have two columns range of numbers i need to list each number in the range > start end > 5 9 > 15 19 > 20 29 > i need for each row to show the numbers in the range > 5 > 6 > 7 > 8 > 9 > 15 > 16 > . > . > pls help > > Was this post he...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Filtered Form Query
I have a form that is filtered using Allen Browne's ajbFindAsUType module (http://allenbrowne.com/AppFindAsUType.html). I want to now create a query that utilizes only the filtered records as its source in order to perform a computation. Is there a way to pass the filtered Recordset into a query via a command button? You can use the RecordsetClone of the form to step through the records. You may be able to pass the Filter from the form on to whatever other operation you need to perform. For example, you could create a report that summarizes the data, and open it like ...

Run a query based on user input to a text box
I am creating a database to manage Auction Bids for a live radio station phone auction weekend. I have a form where the user is entering names as the bidders call in. I want to be able to have entry into a text box cause a query to run and populate a list box so that if a caller has been entered before, the user can click on an existing name. So for example, if the first keystroke is A , then the list box will be populated with all names beginning with A. If the next keystroke is l to give Al then the list box will be repopulated with only names beginning with Al (e.g. Alice, Aliso...

How do I get our Outlook accts to accept emails froma specific dom
I know how to "Accept this domain" in each individual Outlook accout, however, when we add new Outlook accounts we have to go through the process each time. Someone in the office suggested getting a list of all of the emails from that domain and then updating the server. Would this be the best way to do this? Is there a way to get the main Outlook account to accept the domain name and then each time that we add a new account it will automatically accept emails from this domain? What version of Outlook? Do you have the junk mail setting refusing all mail except f...

Can't edit data in query
Hi everyone. Using A02 on XP. This may be a dumb question but I have a UNION ALL SELECT SQL query showing me all records from 3 tables [tDistributions], [tDistributionsArchive] and [tDistributionsArchiveOld]. Why can I not edit any fields? I receive an Excel file with uncashed check numbers, date mailed and payable to. I have to search through all distribution records to identify which distribution record's check did not cash yet so we can pursue payment. Because the tables are old and inherited, my unique identifier is only unique in 2 of the tables so there is a bit of sea...

Display Query Results on top of a Form
I have a query which runs from a control button on a form. When the query runs, the results are displayed behind the form. The user then has to minimize the form in order to see the query results. How do you set the query results to display on top of the form? You could put the following line in the code for the On Click event of the button being pushed: DoCmd.Minimize That will minimize the form and the query should be on top. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Kristin M" wrote: &...

How to change left bottom note that tell number records found?
Hi, At the left bottom corner of Exel 2003 spreadsheet, a display note 'Ready' remains as it is, though I have filtered the items using the filter command, the note usually displays number of records found. How to return back the display note 'number of records found' in Excel 2003 spreadsheet? -- Thank you, Cpviv If you have any formulas that read these filtered records, you will typically not be able to view the # of records found. If you go to Tool - Options - Calculation and change to Manual, you would be able to view # of records. Beware, as this will not au...

Formsand queries
Through a query that uses grouping, I am gathering info from a City table. The table has two fields, the key field and the field City name. Sometimes, the query outputs two or more records. I would like to show the output of the query in a form, not as three separate fields, but as one field, separated with commas. Sample of query output: 534107106 Amsterdam 534107106 Muiden 534107106 Abcoude Sample of output in form: Amsterdam, Muiden, Abcoude What VBA code do I need to do in the form (and which event) to reach this result? Ron RoBo wrote: >Through a query that uses grouping, I a...

Only accept mail from people in my address book
How can Outlook be configured only to accept mail from addresses in my address book. Or how can it automatically delete mail from addresses not in my address book? On 10/22/2003 7:55 PM, DCJ wrote: > How can Outlook be configured only to accept mail from > addresses in my address book. Or how can it automatically > delete mail from addresses not in my address book? I don't think this can be done. Other members may have ways to do this. Sorry that I can be any helpful. Good luck. Alex -- -( Alex Yu | Systms Admintr | Multmd Devlpr | AEM | RPI | MSFT MVP )- What v...

can you count the number of letters (inc spaces) in a single sell
I have a spreedsheet which has 3000 rows. Each cell has a different amount of words in it... ie ABC Learning Centre Ferrymead. Is it possible to count the number of characters (letters) including spaces in each cell??? (please note each cell has a different number of letters in it)... or do I have to do this manually? none of the help options seem to help me... Thanks use the formula =len( -- PF Wannab ----------------------------------------------------------------------- PF Wannabe's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2579 View this thread: ...

Can't see categories in Budget Planner/Won't load Monthly Budget report
In the last couple of days, Money stopped showing the categories under the budget details. In the "Review your current budget status" screen, under budget details, it usually shows each income and expense category and how I'm doing so far in the month. Now none of that shows. I have made sure to check View all budget items and show expense and income in the view menu, but this does not help. In addition, Money will no longer load the monthly budget report when I click "Other Tasks, view reports, monthly budget." I have this problem no matter what budget I...

Sort numbers alphabetically
Let's say I have a list: 1 2 5 20 35 I want it alphabetical, ie: 1 2 20 35 5 Even if I make them text, they still sort numerically. Is there some trick? Thanks, Joe One method................ In B1 enter =LEFT(A1) Copy down. Copy and paste special>values. Select column A and B and sort on column B Gord Dibben MS Excel MVP On Tue, 26 Aug 2008 14:19:10 -0700 (PDT), Pungh0Li0 <joe.dellwo@gmail.com> wrote: >Let's say I have a list: >1 >2 >5 >20 >35 > >I want it alphabetical, ie: >1 >2 >20 >35 >5 > >Even if I make them text...

convert month text (MAR) to month number (3)
I am trying to work with a data set that unfortunately has spit out all the dates in a text format - i.e. 03/01/2009 is MAR 3 2009. Is there a way to convert that text date to an actual date format? Any advice or suggestions are greatly appreciated. Thanks -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200912/1 Debug.Print CDate("MAR 3 2009") = 3/3/2009 One problem with the CDate function is that it will bomb out on things that can't be evaluated as a date. Therefore you may want to use the IsDate fun...

Append Query Problem
Hello, I have a query that calculates a bank balance. I want this query to append the current days date and the bank balance to a table. I have a macro setup to autorun the query when my database opens, but can't get it to work correctly. Details: Table where I want the records added: BankingBalanceRecord Query that will run: BankingRecordBalance -This query is setup to pull today's date and the balance. It is funtioning properly. The main problem I am having is that if the primary record (Today's date) already exists, it gives me an er...

Count 350 SS numbers, exclude duplicates
I need to be able to count information based on approximately 350 social security numbers, exlcuding duplicates. Any ideas on the formula? Thanks so much, Marsha Hi Marsha, One way =SUMPRODUCT((A1:A200<>"")/COUNTIF(A1:A200,A1:A200&"")) -- HTH RP (remove nothere from the email address if mailing direct) "Marsha" <Marsha@discussions.microsoft.com> wrote in message news:E362D9A0-8A06-43E4-81DD-0B2F28C6C18E@microsoft.com... > I need to be able to count information based on approximately 350 social > security numbers, exlcuding duplic...

Cannot see mail in outlook 2000 sent items folder
Hello, I have a weird problem. When I sent a mail, a few days after it desappears from the Sent Items folder. I cannot see any mail I have sent before. But when clicking on the Sent Items icon the folder appears empty and on the bottom of the left side I can see that the folder contains 20 items. Someone else with rights on my mailbox can see those sent items. Any idea? Thanks in advance. ...

mulltiple column "not in" query
Is it possible to perform a multiple column "not in" query in access? I created the query select * from climktvaluefromgroups c where (c.group, c.invdate, c.marketvalue, c.clicode) not in (select t.group, t.invdate, t.marketvalue, t.clicode from tempclimkt t) and I got the error: You have written a subquery that can return more than one field without using the exists reserved word in the main query's from clause. Is this not possible? You cannot do what you are attempting. You MIGHT be able to use concatenation like this: select * from climktvaluefromgroup...

Word 2007
In Word 2003 you can define a new number format list with Number style (None). It is not possible in Word 2007 because the OK button is not activated when (None) is choosen. To create a list like this is useful if you want to repeat a text on every line in a list without a number. My work around for this time was to do a list in Word 2003 version and then copy it into Word 2007. I look forward to get an answere from someone out there. Best regards, Lisa It is true that you can't do this for simple lists in Word 2007, but you can do it for multilevel lists; see h...

Changing the order of page numbers
Example: On a 10 page doc. you want the first 5 pages to be numbered '1' to '5' then the next 5 pages to be numbered '14' to '18' Is there a way of changing page numbers like the above example? Thanks! Ben Ben Don't understand why you want to do this but the best way is to record a macro while you do it manually. 1. print pages 1 to 5 2. go into page setup and change the first page from auto to the value 9 3. print pages 6 to 10. The page count should start at 14. 4 go into page set up and change the first page back to auto. you'll get so...

#DIV/O! Errror with Absolute Number Calculation
Hi, I am calculating the following formula. =ABS(K41-J41)/ABS(K41) The result is #DIV/O! many times because K41 is 0 and the result is #DIV/O! I'd like the result to be "N/A" or something that I could put in between parenthesis. I'd be happy with any recommendations you have. Thanks!! Try this... =IF(K41="","N/A",ABS(K41-J41)/ABS(K41)) -- Biff Microsoft Excel MVP "Brent" <Brent@discussions.microsoft.com> wrote in message news:93A3C05A-89EC-4229-BA99-C23EF2E24AA8@microsoft.com... > Hi, > > I...

Is there a cell format for HEX numbers?
Hello. Is there a way to format cells for HEX numbers? I currently have to format them as text. I would like to be able to automatically increment in HEX, as I can with general numbers or dates. Is this possible without writing a macro? Betty wrote: >Hello. > >Is there a way to format cells for HEX numbers? I currently have to format >them as text. I would like to be able to automatically increment in HEX, as I >can with general numbers or dates. Is this possible without writing a macro? > > To display a decimal number as hex use: [ ] = DEC2HEX(A1,n) If it...

unable to see images in OWA using exchange 5.5.
when accessing mail using OWA in exchange 5.5 one or more pictures (graphics or images) may not be displayed. Instead, a red X, or a placeholder, may appear in place of the images. rgds, biren ...