1. List patient numbers along with patient address information: SELECT Patients.PatNo, Patients.Address, Patients.City, Patients.State, Patients.Zip FROM Patients Results: Query2 Patient No. Address City State Zip 12 Corona CA 91112 13 818 Rivera Court Santa Ana CA 92233 14 12 Caballero Rd. Tempe AZ 81122
2. What diagnosis is most often used? SELECT Diag_Code, COUNT(*) FROM Admissions GROUP BY Diag_code ORDER BY Diag_code Results: Query1 Diagnostic Code Expr1001 A1 3 A2 3 B3 2 3. What is the average stay for a patient?** **This one is a little tricky and it is important to note that there can be more than one solution, which is often the case in SQL or computer programming in general. Since we are using Microsoft Access, we can take advantage of a built-in function called “DateDiff” that allows one to calculate the time difference between two dates and return the results in various time units ( ). Here, in the first step I use this function simply to calculate out the patient stays and then order these lengths of stay in descending order: SELECT DateDiff("d",[Admit_date],[Dischg_date]) AS total FROM Admissions ORDER BY DateDiff("d",[Admit_date],[Dischg_date]) DESC; Results: Query 1 Total 58 30 27 2 1 1
Query 1 Total 0 0 To use this code to determine the average length of stay, I use the above code in a subquery, which is accomplished as shown below (although not required, it is common to enclose subqueries in parentheses; ) . SELECT AVG(total) FROM (SELECT DateDiff("d",[Admit_date],[Dischg_date]) AS total FROM Admissions) Results: Query1 Expr1000 14.875 4. What is the average co-pay at the hospital? (With the values present, this is a much easier calculation) SELECT AVG(Copay) FROM Admissions; Query1 Expr1000 $22.63 5. Determine each patient’s total bill (total std. cost) SELECT Admissions.PatNo, COUNT(*) AS services, SUM(Diagnostics.Cost) AS bill FROM [Diagnostics] INNER JOIN Admissions ON Diagnostics.DiagNo = Admissions.Diag_Code GROUP BY Admissions.PatNo ORDER BY Admissions.PatNo
Query1 Patient No. services bill 12 3 $150.00 13 2 $100.00 14 3 $150.00 Part 1 (15 points) Download HHA_database.accb, open in Microsoft Access 2010. This file is a database derived from an Excel spreadsheet available at . The database contains responses to the CMS survey of U.S. Home Health Agencies (both nationally and abroad) regarding their services. All U.S. HHAs are listed in the HHA_Providers and HHA_Services tables, while agencies represented in the HHA_surveys table are the only ones to offer complete response to the following questions: HHA_Survey_Questions ID Field1 1 How often the home health team began their patients care in a timely manner? Use SELECT Sum(HHA_surveys.Question_1) AS SumOfQuestion_1 FROM HHA_surveys; and the answer is 674,865 times.
- Fall '19