Database and Spreadsheet Applications for Improved Healthcare Management

1. Create a new file: Use MS Access and name the file sno_admin_name.accdb (replace sno with class register number, admin with student ID, name with your name as in class register).
2. Create a database: Create two tables—Doctor and Patient—by importing tables from the Excel file weesongpolyclinic.xlsx.
3. Assign appropriate data types: Set the appropriate data types and field lengths for fields in both tables.
4. Set metadata: Identify and set metadata, such as Primary Key and Referential Integrity (Foreign Key).
5. Create the following queries:
   • Query named Weight watchlist: Show Patient_ID, Name, ContactNum, and Weight where Weight is greater than 75kg.
   • Query named Patients of Dr Rachel Lim: List all the patients of Dr. Lim. Show only Patient_ID, Name, ContactNum.
   • Query named Patients with allergies: Show Patient_ID, Name, Gender, Address, Postal Code, ContactNum, Allergies, dName for patients with allergies.
6. Create a report: Create a report named “Patients with allergies” based on the query. Use the landscape layout and follow the heading format as shown in the screenshot.

Question 2 – Spreadsheet Application

You are to lead a team with the goal of improving hospital quality services for Mountain View Private Hospital Endoscopy Department. Your team is to identify areas for improvement. Based on the team’s suggested areas of improvement, the team is to:

• Formulate a business question: Identify what you want to find out (e.g., what is the trend like? Which sector is doing well or badly?).
• Suggest relevant indicators and data to collect: Choose indicators such as patient wait times, procedure success rates, patient satisfaction scores, etc.
• Design a spreadsheet or table of variables: Create a table to collect data, such as Patient ID, Procedure Type, Wait Time, etc. (sample size 25-30).
• Show a sample of the design spreadsheet/table and its data collection (sample size 25-30)
• Create a sample dashboard: Design a dashboard to visualize the data and address the business question.
• Make recommendations: Provide suggestions on how to improve the service quality based on the analysis.

In your report, include the following:

• Background of the case: Provide context about the hospital and the areas needing improvement.
• Explain the rationale of the business question: Justify what you aim to achieve with the business question.
• Justify the selection of relevant indicators: Explain why the selected indicators are important to answering the business question.
• Explain how the dashboard answers your business query: Describe how the dashboard helps in visualizing the data and addressing the business question.
• Explain how your recommendations are going to improve service quality: Provide a detailed explanation of how your recommendations will help improve the hospital’s services.