Writing Queries in Medical Office.accdb

From the folder “Exam Files” on Scholar download on your computer file “Medical Office.accdb” and save it on your computer as “Your_First_Name_YourLastName_Medical Office.accdb”.  For example, if your last name is John Smith, the file should be named “John_Smith_Medical_Office.accdb”.

Picture below illustrates tables in the database and relationships among them.

Write the following queries, and save them.  When done, email me the file.

• (8 points)  In the Access file, create a new query which selects all appointments dates for the patient Barbara Jones.  The following fields should be selected:
• Patient Last_Name
• Patient First_Name
• Appointment_Date

Use tables’ aliases in your query.  The results should display in chronological order, earliest appointments first.

Save the query as “Barbara_Jones_Appointments_Query”.

• (10 points)  Create a new query which selects all appointments served by doctor Charles Schmidt.  Specifically, select the following fields:
• Patient Last_Name
• Patient First_Name
• Appointment_Date:
• Doctor Last_Name
• Doctor First_Name

Use tables’ aliases in your query.  The results should display in chronological order, earliest appointments first.

Save the query as “Charles_Schmidt_Appointments_Query”.

• (12 points)  Create a query which selects all patients who scheduled a new patient appointment.  Specifically, select the following fields:
• Patient Last_Name
• Patient First_Name
• Appointment_Date
• Procedure_Description
• Procedure_Cost

for the procedure description “New Patient Appointment”.  Use tables’ aliases in your query.  

Save the query as “New_Patient_Appointment_Query”.

• (4 points)  Each appointment has a field “Status”, which is either a “No-Show”, when the patient does not show up, or a “Show-Up”.  The medical office is suspecting that patient no-shows is starting to become a real problem.  If excessive number of patients do not show up, the office may start to charge patients a no-show fee.  Create a query which will calculate counts of show-ups and no-shows.  HINT: the query should use a GROUP BY statement.  Use tables’ aliases in your query.  

Save the query as “Appointment_Status_Count_Query”.

• (6 points)  The medical office would like to know how much revenue each procedure brings.  Each time when a procedure is performed during an appointment, the insurance company is invoiced the amount equal to the procedure cost (a field from table Procedures).  Write a query which for each procedure calculates total revenue, generated by this procedure.  To do that, the query should for each procedure add procedure cost as many times as the procedure was performed.  HINT: the query should use a GROUP BY statement.  Make sure to list all procedures names together with total revenue, generated by each procedure, staring from highest total revenue to lowest.  Use tables’ aliases in your query.

Save query as “Procedures_Total_Revenue_Query”

• (10 points)  The medical office would like to know how much revenue each specialty brings.  Each time a doctor serves an appointment, and a procedure is performed, insurance company is invoiced a procedure cost.  For each doctor specialty (a field in Doctors table), calculate total revenue generated as a sum of all procedure costs for appointments performed by this specialty.  Your query should list all specialties, and total revenue generated by each of them.  HINT: the query should use a GROUP BY statement.  Make sure to list specialties starting from highest total revenue generated to lowest.  Use tables’ aliases in your query.  

Save the query as “Specialties_Total_Revenue_Query”

When you are done with all queries, save the accdb file and email it to me.

Make sure to email to me the actual accdb file, and NOT accdb record-locking file (which is typically about 1K in size).