Assume the speciality description provided parameter

Instructions: Save your database file for Part A as [YourStudentNumber]LMC.accdb e.g. 555667LMC.accdb and upload together with your solution for Part 2 below through WebCT (the submission link will be under Practical Computer Lab Work)

PART 1

The spreadsheet system is inefficient and there is clearly a problem with data redundancy and integrity. Numerous errors are being made in capturing data. Patients are unhappy with the accuracy of their bills and are becoming increasingly dissatisfied with the services being provided by LMC.

Tables

1a) Import data from LMC_data.xlsx to create and populate certain of the required database tables in Microsoft Access.

Queries and Reports

From your discussions with the head of operations at LMC, you identified that the system needs to produce certain reports. First you will need to create the Access queries that are required for each report, and then using the report wizard generate the reports. Reports and queries should be given the same name.

Demand report: A report detailing the patients who have consulted the various doctors. The report should be grouped by doctor listing patients alphabetically by surname. Save this query as “PatientListByDoctor”

(5 marks)

Online query: A list of doctors with specific specialization. Assume the speciality description is provided as a parameter. Save this query as “SpecialityLookUp”

(3 marks)

Lifestyle Medical Clinic (LMC) is expanding to include additional medical services. To accommodate the new services, they will be building a new wing onto the clinic. The managers at LMC need to make a decision on the optimal amount of space that they should allocate to each of the following three services: a pathology laboratory; a radiology department; a rehabilitation centre. Each of these services will be run by external providers who will pay monthly rental to LMC (per square meter) as well as paying over to LMC a share of their revenue.

LMC needs to know how much space it should allocate to each area within the new building in order to maximize potential future revenue. The following table presents the costs associated with the building of each area; the monthly rental income; and the share of revenue.

1. The total available space is limited to 1,200 square meters

2. Pathology has indicated that they need at least 250 square meters of space, but would prefer 400 square meters but above which they would not be able to afford the rental

1. Based on the given figures, LMC management would like to know how much space to allocate each of the three services in order to maximize monthly revenue. Using the Excel Solver, help the managers of LMC with their decision problem.

2. Based on your solution, indentify the services that are not likely to get their preferred space allocation?