Microsoft Access database for a Patient Index and Management Reports


Please use Microsoft Access 2016, and follow the instructions to do this assignment. Please send me the Microsoft access file when you finish. Make sure you address all requirements. Review the lecture PDF first to know how to do the assignment.

After reading the Exercise File ‘Computer-Based Information Systems for a Hospital Emergency Department (RAND 1977),’ imagine that you are the Information Systems Manager responsible to implement Santa Monica Hospital’s ‘Patient Index and Management Reports’ system BUT in 2018 NOT 1977.
Your deliverable should be in the following format, uploaded to Canvas: Access 2016 (Windows) database (.accdb). All other file types will NOT be accepted. More information about databases can be found at:
Grading Criteria:
60 Points – Table Design: Fields, Data Type, Primary and Foreign Keys
Table Records: Physician (min 10), Patient (min 20), Visit (min25), Your Choice (min 10)
20 Points – Patient Information Table relationship to Patient Visit Table
Your Additional Table relationship to any other Table
30 Points – Forms (10 points each)
30 Points – Queries (10 point each)
10 Points – Reports (5 points each)
Assignment Requirements
You are to create a Microsoft Access database for a ‘Patient Index and Management Reports’ system needed by Santa Monica Hospital.  Use techniques and skills from the and from Microsoft Access eTraining videos
 Ensure the file is readable in Access 2016 for Windows.
Table (x4)
Tables should be detailed and thoughtfully constructed. Tables should use the data types that best match the field information.
 Physician Information Table (minimum 10 records)
o Primary Key: Employee Number
o Fields (at least): First Name, Last Name, Age, Race, Sex, Specialization, Photo (attachment)
 Patient Information Table (minimum 20 records)
o Primary Key: ID (NOT Social Security Number)
o Fields (at least): First Name, Last Name, Age, Race, Sex, Marital Status, Zip Code, Disposition (admitted, transferred, or outpatient), Category (medical or surgical)
 Patient Visit Table (minimum 25 records)
o Primary Key: Visit Number (auto-number)
o Foreign Key: Patient ID (from the Patient Information Table)
o Fields (at least): Date, Day of the Week, Arrival Time, Departure Time, Diagnosis, Fee Category (private pay, insurance, emergency aid program, SMH Employee), Fee Paid
 One Additional Table of Your Choice (be creative, min 10 records)
o Primary Key, Foreign Key, Fields (at least 5)
Table Relationship
 Patients who have a record in the Patient Information Table should be assigned a visit number for each unique visit to the ER. Full credit will be given to projects that enforce referential integrity, creating a one-to-many relationship.
 Your ‘Additional Table’ should be related to any other table, creating a one-to-one or one-to-many relationship.
Form (x3) All forms must have a thoughtful design (use Design View)
 Input Physician Information Form (include ‘Photo’ field)
 Input Patient Information Form
 Input Patient Visit Form
Query (x3)
 Multiple Table Query
o Patients by day of the week and patient category (less complex but similar to RAND Figure 2)
o Patients in each fee category (less complex but similar to RAND Figure 3)
o One Additional Query of Your Choice (be sure it contains fields from your Table and one additional Table)
Report (x2)
 Table Report
o The ‘Patient Information Table’ (thoughtfully formatted)
 Query Report
o The number of patients by day of the week and patient category (thoughtfully formatted)