MS ACCESS

ASSIGNMENT5DUEFEB9.docx

Home >Computer Science homework help >MS ACCESS

Healthcare Database Development using MS Access

For this assignment, you will create a database that includes a table, form, report, and queries using MS Access.

Scenario:

A small healthcare facility still using the paper-based medical record, needs an MS Access database to collect, maintain, and search providers' and patients' information.

Create MS Access database with the following information.

1. Create a New blank database

2. Name the Access file as your last name in all lowercase letters. (For example, Janet Learner would save her file as “learner.accdb”) (Be sure to “name” the database before hitting the “Create” button)

3. In Design View, create a table using the structure shown below:

a. Name the table: Patient Table

b. Set the Patient # field as the Primary Key

Patient Table

Field Name	Data Type	Field Size	Other Field Properties
Patient # (Primary Key)	Short Text	5
Last Name	Short Text	Default
First Name	Short Text	Default
Address	Short Text	Default
State	Short Text	2	Format: Upper case Default value: IL
Zip Code	Short Text	5
First Visit	Short Text	4
Balance	Currency	Default

4. In Data sheet View, add the following data to the Patient Table.

Patient #	Last Name	First Name	Address	State	Zip Code	First Visit	Balance $
54783	Williams	Jack	62 Smith Lane	MD	21202	2019	250
64589	Metheny	Alexa	125 Fairview	MD	21203	2019	200
95874	Van Wegan	Alison	100 Quantico	MD	21204	2019	350
23895	Jackson	Ryan	2320 Hills Circle	VA	20147	2020	325
96312	Berstein	Krista	126 South Street	VA	20148	2020	290
54387	Wylliams	Karen	43 Jones Ave.	VA	22193	2021	200
54123	(Your name)	(Your name)	(Your address)	VA	22191	2021	315

5. Modify the column width as necessary

6. Create a Form using the Form Wizard based on the Patient table with these specifications:

a. Use All Fields

b. Layout: Columnar

c. From Name: Patient From

7. Add the following records to the Patient Table using the From.

Patient #	Last Name	First Name	ADDRESS	city	state	Zip Code	First Visit	Balance
36987	Booy	Aaron	21 Fairview Lane	Ashburn	VA	20146	2020	250
76213	McBurney	Colton	45 Trotters Drive	Hagerstown	MD	21522	2022	350

8. When finished adding the records, save the Form as Patient From.

9. Using the Report Wizard, create a report based on Patient Table with the following specifications.

a. Use All Fields

b. Group by: First Visit

c. Layout: Stepped

d. Orientations: Landscape

e. Title the report: Patient Report

f. Adjust column width in Design View as necessary.

10. Create Queries

a . Query 1: Create a query from the Patient Table using the Simple Query Wizard.

i. Generate a report with names and patient numbers of the patients who visited in 2019

ii. Select the appropriate fields and the appropriate criteria.

iii. Run this query

iv. Explain how these results can be used by the management.

b. Query 2: Create a query from the Patient Table using the Simple Query Wizard

i. Generate a billing statement of patients who owe more than $300

ii. Select the appropriate fields and the appropriate criteria.

iii. Run this query.

iv. Explain how these results can be used by the non-clinical management.

11. Save your completed Access file and upload to the week 5 assignment folder.

12. Reflect on the process, its challenges and potential pitfalls in the database utilization by the clinic. Would you offer an alternative? Why?

Your APA formatted assignment comprising 2-4, double-spaced, typed in 12-point Times New Roman (or 11- point Calibri) excluding the Cover and Reference pages should be submitted by Tuesday mid-night. 2 credible sources should support your conclusions on queries. Follow APA 7