Health Analytics Database Project
Kpatel
LectureCreatingaDatabase.pptxDepartment of Health Informatics
Health Information Management Program
BINF 5520 Health Analytics
Creating A Diabetes Tracking Relational Database
Using Microsoft Access
Fundamentals of Creating A Clinical Tracking Database
Working With Database “Objects”
Tables
Forms
Queries
Reports
Creating a Database to Track Patients With Diabetes
Review of Database Fundamentals
Questions and Answers
How This Presentation Is Organized
Step Number Will Always Be At Top
Command Orientation in Red on Left Side
Screen Shot In Middle
Arrows will focus your attention.
The Four Objects of Microsoft Access
TABLES: The “Containers” That Hold The Data. We must DESIGN these tables before we can do anything, because they hold the data !
FORMS: The Forms allow us to display information to users easily.
QUERIES: The Queries allow us to select data based on specific criteria.
REPORTS: The Reports allow us to output data, either via printer or via a file, such as files that are in a PDF or XLS format.
The Four Objects of Microsoft Access
TABLES
QUERIES
REPORTS
FORMS
DATABASE
The Five Steps of Creating A Relational Database
1. Create the Tables
2. Define The Database Relationship(s)
3. Create The MPI and Encounter Forms
4. Combine the MPI and Encounter Forms Into One Form
5. Start Using The Database !
1. Create the Tables
Master Patient Index (MPI)
Field Name Field Type Field Length
PtId AutoNumber Numeric
PtLast ShortText 30
PtFirst ShortText 30
PtDOB Date MM/DD/YYYY
MRNumber ShortText 12
PtSex ShortText 1
PtRace ShortText 1
And other fields….
Encounters
Field Name Field Type Field Length
EncounterID AutoNumber Numeric
PtId Number Numeric
DateOfService Date MMDDYYYY
Provider ShortText 30
A1C Numeric Decimal,0
BP-Systolic Numeric Decimal,0
BP-Diastolic Numeric Decimal,0
Cholesterol Numeric Decimal,0
Retinopathy Yes/No Yes/No
Neuropathy Yes/No Yes/No
And other fields….
2. Define The Database Relationship(s)
Master Patient Index (MPI)
Field Name Field Type Field Length
PtId AutoNumber Numeric
PtLast ShortText 30
PtFirst ShortText 30
PtDOB Date MM/DD/YYYY
MRNumber ShortText 12
PtSex ShortText 1
PtRace ShortText 1
And other fields….
Encounters
Field Name Field Type Field Length
EncounterID AutoNumber Numeric
PtId Number Numeric
DateOfService Date MMDDYYYY
Provider ShortText 30
A1C Numeric Decimal,0
BP-Systolic Numeric Decimal,0
BP-Diastolic Numeric Decimal,0
Cholesterol Numeric Decimal,0
Retinopathy Yes/No Yes/No
Neuropathy Yes/No Yes/No
And other fields….
3. Create The MPI and Encounter Forms
4. Graft the MPI and Encounter Forms Together
5. Start Using The Database !
Step 1
Step 2
Create / Table Design
Step 3
Create / Table Design
Step 4
Create / Table Design
Step 5
Create / Table Design
Step 6
Create / Table Design
Step 7
Create / Table Design
Step 8
Home
Step 9
Create / Table Design
Step 10
Create / Table Design
Step 11
Create / Table Design
Step 12
Create / Table Design
Step 13
Create / Table Design
Step 14
Create / Table Design
Step 15
Home
Step 16
Database Tools / Relationships
Step 17
Database Tools / Relationships
Step 18
Database Tools / Relationships
Step 19
Database Tools / Relationships
Step 20
Database Tools / Relationships
Step 21
Database Tools / Relationships
Step 22
Database Tools / Relationships
Step 23
Database Tools / Relationships
Step 24
Create / Table Design
Step 25
Create / Table Design
Step 26
Create / Table Design
Step 27
Database Tools / Relationships
Step 28
Home
Step 29
Home
Step 30
Home / Right Click on Encounter / Left Click on Design View
Step 31
Move to Provider Field and go to Tab at Bottom called Lookup
Step 32
In Tab at Bottom called Lookup, Select Combo Box
Step 33
In Row Source option, select lkpProvider Table developed earlier.
Step 34
We now save the table by selecting Yes.
Step 35
We will now see the two tables and the relationship between the tables.
Step 36
Design / Relationships / Save / Yes
Step 37
We now see all three tables: MPI, Encounter, and lkpProvider
Step 38
Create / Form Wizard
Step 39
Create / Form Wizard
Step 40
Create / Form Wizard
Step 41
Create / Form Wizard
Step 42
Create / Form Wizard
Step 43
Home / Right Click on Form MPI, Left Click on Design View
Step 44
Highlight the four fields at the bottom left side of the screen and move to upper right.
Step 45
Highlight the four fields at the bottom left side of the screen and move to upper right.
Step 46
Highlight the four fields at the bottom left side of the screen and move to upper right.
Step 47
Close the Form MPI and Left Click Yes to save the changes to the design of the form.
Step 48
Highlight the four fields at the bottom left side of the screen and move to upper right.
Step 49
Create, Form Wizard, Left Click on Form Encounter, Right Click on Design View
Step 50
Click the double right arrows (>>) to move from Available to Selected and click Next.
Step 51
Click Next to display all fields for this form.
Step 52
Indicate that the form should be organized in a Tabular layout and click Next.
Step 53
Name the form Encounter and click Finish.
Step 54
The form will organize horizontally. You may need to adjust the width of fields to enhance the readability of the form.
Step 55
Close the form and click Yes to save the changes to the design of the form Encounter.
Step 56
On the left side of the screen, left click on the Form MPI and right click on Design View.
Step 57
You will see the large area under the MPI fields. This is where we will move the Encounter form so that we can simultaneously see the Patient and all associated encounters.
Step 58
We then left click on the Form Encounter and we position it under the PtFirst field in the MPI form.
Step 59
We then close Form MPI and we click Yes to save all changes to the design of this form.
Step 60
We can now double click on the MPI form and we will see how the two forms have been joined together.
Step 61
The screen below shows you the results of a database that has been populated. Note that the PtId in the MPI is the same as the PtId in the Encounter.
