16 Feb 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