This assignment will demonstrate student proficiency in creating and importing data into tables, performing queries and designing reports.
1. a) Using MS Access and principles learned in the online tutorials (Sessions on 10/31 and 11/7), create a database called MR_Abstracts.
b) Import data from the three MS Excel files (Patients.xlsx; Clinical.xlsx; and MD.xlsx) into the MR_Abstracts database you just created. The import will result in three tables being created in the database (Patients table, Clinical table and MD table). You will need to define the fields in the tables by ensuring the correct field data type (e.g. date fields vs text fields) and assigning a primary key field to each table.
c) Create simple forms for data input into the tables (call them Patient Input Form, Clinical Input form, and MD Input Form).
d) Establish relationships between tables by enforcing referential integrity
You should have a one-to-many relationship between the Patients table and the Clinical table through the common field MR# (primary key in Patients table and foreign key in Clinical table).
You should also have a one-to-many relationship between the MD table and the Clinical table through the common field MD_Code# (primary key in the MD table and foreign key in the Clinical table).
2. Change the name of the first patient (of your sex) from Evanston to your name.
3. a) Your hospital Marketing Department is doing a mailing and wants a list of all patients in Evanston. Perform a query on the Patient table and include ZipCode, LastName, Address, City, State, and MR#.
b) Sort the data by ZipCode in ascending order and within ZipCode ascending order of LastName.
c) Create a report using the results of your query. Save the query as Evanston Query and the report as Evanston Report.
4. a) Using all three tables, perform a join query that lists Dr. Stevens' patients with Myocardial Infarctions (410...). Save the query you just developed as Dr Stevens Patients with MI Query.
b) Use the query to create a report and save it as Dr Stevens Patients with MI Report. Make sure it includes the following data elements in logical order (not necessarily in order listed): MR#, LastName, AdmitDate, Disch Date, MD_LastName, PrinDxCode, PrinProcCode.
5. a) Create a query called MDs by Service Query and use the query to create a report that is sorted by Service and MD_LastName. Save the report as MDs by Service Report.
b) Be sure to include in logical order (not necessarily in this order) in your query and report: Service, MD_LastName, MR#, AdmitDate, Disch Date, PrinDx, PrinProc.
6. a) Create a query and use it to create a report of all patients sorted by LastName that includes their MR#, admission and discharge dates, Length of Stay (LOS), as well as their attending MD_Code#. (Hint: You will need to add a calculated field for LOS in your query).
b) Save the query as Patients Query and use it when you create the Patients Report.
Clinical.xslx: see image.
MD.xlsx: see image.
Patient.xlsx: see image.