ann arbor asa ‘up and running’ series: access
DESCRIPTION
Ann Arbor ASA ‘Up and Running’ Series: ACCESS. Prepared by volunteers of the Ann Arbor Chapter of the American Statistical Association, in cooperation with the Department of Statistics and the Center for Statistical Consultation and Research of the University of Michigan. PURPOSE: - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/1.jpg)
Ann Arbor ASA‘Up and Running’ Series:
ACCESS
Prepared by volunteers of the Ann Arbor Chapter of the American Statistical Association,
in cooperation with the Department of Statistics and the Center for Statistical Consultation and Research
of the University of Michigan
![Page 2: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/2.jpg)
2
PURPOSE: • INEFFICIENCIES of Data Storage in Excel• ADVANTAGES of Data Storage in Access• Simple Access Database:
Creation of tables:doctor_information, patient_information,
patient_appointment Manual Data EntryForeign Keys
• Data Entry Screens and Data Reporting
![Page 3: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/3.jpg)
3
Inefficiencies of Excel
Excel Spreadsheet Inefficiencies
• Disorganized, multiple entries for Dr. Peg Black.
• Tedious cutting and pasting required for reports on Dr. Peg Black.
• Difficult tracking of monthly, weekly and daily appointments
• Not multi-user• Tedious data entry
![Page 4: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/4.jpg)
4
Inefficiencies of Excel (continued)
Doctor Patient Appointment Dr. John Smith Jane Dwight 11/2/2009 8:00am Dr. Peg Black Bob Carlson 01/2/2009 8:00am Dr. George Frank Peter Townsend 3/2/2009 8:00am Dr. George Frank Peter Townsend 3/3/2009 8:00am Dr. George Frank Linda Garner 3/4/2009 11:00am Dr. George Frank Linda Garner 3/4/2009 1:00pm Dr. George Frank Robert Hendrick 2:00pm 3/4/2009 Dr. George Frank Robert Hendrick 2:00pm 3/5/2009 Dr. Peg Black Bob Carlson 01/3/2009 8:00am Dr. John Smith Jane Dwight 11/3/2009 9:00am Dr. George Frank Lucy Schwartz 11/4/2009 9:00am Dr. Peg Black Bob Carlson 01/3/2009 8:00am Dr. George Frank Linda Garner 9/4/2009 1:00pm Dr. Peg Black Bob Carlson 01/4/2009 8:00am Dr. George Frank Linda Garner 8/4/2009 1:00pm P. Black Bob Carlson 01/5/2009 8:00am
Dr. John Smith Mary Yates 11/4/2009 9:00am Dr. John Smith Jane P. Dwight 11/5/2009 9:00am Dr. Peg Black Bob Carlson 01/6/2009 8:00am
Bob Carlson 01/7/2009 8:00am Dr. George Frank Linda Garner 8/4/2009 1:00pm Dr. George Frank Lucy Schwartz 7/4/2009 9:00am Dr. John Smith Andrew Taylor 11/6/2009 9:00am
Carol Henderson 01/8/2009 8:00am Dr. Peg Black Carol Henderson 01/9/2009 8:00am Dr. Peg Blck Carol Henderson 01/10/2009 8:00am Dr. John Smith Cal Hanks 11/7/2009 9:00am Dr. John Smith Ben Biggs 11/7/2009 9:00am Dr. George Frank Lucy Schwartz 7/8/2009 9:00am
• Inconsistent entries P Black. ,Dr. Peg Black. PREVENT accurate data tracking
• Leaving out an entry Dr. Peg Black PREVENTS accurate data tracking
![Page 5: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/5.jpg)
5
ADVANTAGES of Database Storage in Access: Data Entry Screens
• Data Entry Screens
produce list of valid doctor names
• No room for data entry
mistakes
Look up list Of Doctors Names
![Page 6: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/6.jpg)
6
ADVANTAGES of Database Storage in Access: Data Entry Screens
• Data Entry Screens
produce list of valid patient names too.
• No room for data entry
mistakes
Look up list Of Patient Names
![Page 7: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/7.jpg)
7
ADVANTAGES of Database Storage in Access: Reports
• Reports enable you
to group
Look up list Of Patient Names
Doctor NameEach Patient
![Page 8: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/8.jpg)
8
ADVANTAGES of Database Storage in Access: Applications
• Access Data Entry Screens and Data AccessReports can be linked together to create AccessApplications
![Page 9: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/9.jpg)
9
Access Database:First Access Database Screen
• Table: Spreadsheet – Rows of data– Columns of data
• Database: Workbook– Collection of
Spreadsheets or Tables
![Page 10: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/10.jpg)
10
Simple Access Database
• To start MSAccess Under the Start menu Click on All Programs (Note, items on right side may be different) Click on
All Programs
![Page 11: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/11.jpg)
11
Simple Access Database
• UnderGeneral Office Applications ,Microsoft Office 2007 foldersClick onMicrosoft Office Access 2007
Click on Microsoft OfficeAccess 2007
![Page 12: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/12.jpg)
12
Simple Access Database
• Click On Blank Database Click on
Blank Database
![Page 13: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/13.jpg)
13
Simple Access Database
• Type in the database name (or file name)
“database1”in the File_NameText box.
• Click Create button
• Notice that the directory structure of thedatabase appears above the create andcancel buttons
Directory StructureType in “database1”
Click on Create button
![Page 14: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/14.jpg)
14
Simple Access Database
• This screen appearsWhen you click the Create button.
• At this point you are ready to add fieldsto your first table. Lateron we will save thistable and give it a name
We will give thistable a name lateron
We are adding fields to our newtable
![Page 15: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/15.jpg)
15
Simple Access Database (Table Creation)
• Purpose to Create tables doctor_information,Patient_information, and patient_appointment.
• Add columns, rename columns, and manuallyenter data.
![Page 16: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/16.jpg)
16
Simple Access Database (Table Creation)
Create Table Basics
• AutoNumber: Unique Identifier: generated automatically when you create a database
• Rename Column: Right click and chose rename. Type column name. Click on “add new field”
• Move to next field: Type in the name of the column and hit enter
• Change column data type: Go to the data type list box and choose an appropriate data type. Default is text
ID field created automatically when you create a database
![Page 17: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/17.jpg)
17
Simple Access Database
• Create Doctor_idColumn by renamingID field. Right mouse click.
Choose rename column.
![Page 18: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/18.jpg)
18
Simple Access Database (Move to next field)
• Rename field Type in “doctor_id”.
Double click on “add new field”
![Page 19: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/19.jpg)
19
Simple Access Database (Move to next field)
• Move to next field Type “first_name”. Hit <enter>
![Page 20: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/20.jpg)
20
Simple Access Database (Move to next field)
• Move to next fieldBlank field appears.Cursor is on the blank field
Cursor is on blank field
![Page 21: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/21.jpg)
21
Simple Access Database (Move to next field)
• Add next fieldsAdd next fieldslast_name, address, city, state in the same fashion.Type each field name.Hit <enter>
![Page 22: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/22.jpg)
22
Simple Access Database (Move to next field)
• Add next fieldsAfter adding lastField , state, andhitting <enter> Results are as follows: Cursor is here
![Page 23: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/23.jpg)
23
Simple Access Database (Move to next field)
• Modify data typeAdd numeric fieldZip. Type in zip
Type in Zip
![Page 24: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/24.jpg)
24
Simple Access Database (Move to next field)
• Modify data typeNext,Click on white space.Click on Data TypeList box and choosenumber
(1) Click on white space
(2) Click on DataT ype list box and choose number
Notice “add New field “ label For next field
![Page 25: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/25.jpg)
25
Simple Access Database (Move to next field)
• Modify data type• Double Click on “Add new Field” To the right of “zip”
• RepeatFor the soc number.The resulting screenLooks as follows:
(1) Click on white space (2) Click on DataT ype list box and choose number
![Page 26: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/26.jpg)
26
Simple Access Database (Move to next field)
• Modify data type• Repeat process for hire_date
• Except for the Date type choose Date time
(1) Click on white space (2) Click on DataT ype list box and choose number
![Page 27: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/27.jpg)
27
Simple Access Database (Save Table)
• Save Table• Click on Upper-left corner disk icon
• Type doctor_informationin dialog box.
1) Click on the disk icon to save the table.
2) Type in doctor_information in dialog box
![Page 28: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/28.jpg)
28
Simple Access Database (Save Table)
• Save Table• Press OK in dialog box.
• Results are as follows:
1) Click on the disk icon to save the table.
The table is called doctor_information
![Page 29: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/29.jpg)
29
Simple Access Database (Create table for
excercises)
• To create theTables for the Following exercisesClick on Create
Click onCreate
![Page 30: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/30.jpg)
30
Simple Access Database (Create table for
excercises)
• Next clickOn Table
Click onTable
![Page 31: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/31.jpg)
31
Simple Access Database (Create table for
excercises)
• You are set to Add fields For your new Table.
You are set to add fields for your new table
![Page 32: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/32.jpg)
32
Simple Access Database (Exercise)
• Create tables patient_information and patient_appointment table. The patient_information table has the following layout:
Patient_id Patient_last_name
Patient_first_name
Patient_middle
Patient_soc Patient_address
Autonumber Text Text Text Number text
Patient_state Patient_city Patient_zip Date_of_birth
Text Text Number Date time
![Page 33: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/33.jpg)
33
Simple Access Database (Exercise continued)
• Create tables patient_information and patient_appointment table. The patient_appointment table has the following layout:
• Patient_appointment has the followiAppt_id Appt_date
Doctor_id App_desc Patient_id
Autonumber
Date time Number Text number
![Page 34: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/34.jpg)
34
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Open up doctorInformation tableto enter data
• You can right mouse click on a table on the leftside and click open
1) Click on the disk icon to save the table.
![Page 35: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/35.jpg)
35
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Open up doctorInformation tableto enter data
1) Click on the disk icon to save the table.
1) Right mouse click
2) Click Open
![Page 36: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/36.jpg)
36
Simple Access Database: Manual Data Entry
• DoctorInformation data
• OR double clickon doctor_information : Table
1) Click on the disk icon to save the table.
1) Right mouse click
2) Click Open
1) Double click on doctor_information table
![Page 37: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/37.jpg)
37
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Data EntryScreen Appears 1) Click on the disk icon to save the table.
1) Right mouse click
2) Click Open1) <Tab> past doctor id field on to first name.2) Type first name “Gina”3) <Tab > to last_name enter “Smith” for last name4) Continue tabbing and entering in fields
Cursor is here
![Page 38: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/38.jpg)
38
Simple Access Database: Manual Data Entry
• DoctorInformation data• Data EntryScreen Appears• Doctor_id filled In automaticallyby pressing <tab>Or <enter>
Note: the Doctor_id shownon this slide may be different than whatyou have since the idsare automatically generated.
1) <Tab> past doctor id field on to first name.2) Cursor is here
1) As soon as you start typing on the first_name field …
the doctor_id field is populated
![Page 39: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/39.jpg)
39
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Data EntryScreen Appears
• ContinueTabbing and Entering fields
1) Click on the disk icon to save the table.
1) Right mouse click
2) Click Open1) <Tab> past doctor id field on to first name.2) Type first name “Gina”3) <Tab > to last_name enter “Anderson” for last name4) Continue tabbing and entering in fields
![Page 40: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/40.jpg)
40
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Tab to each field• Enter value• Hit <Tab> or <Enter> to moveTo next field
1) Cursor is at address2) Type “123 Lane” for address3) <Tab> or <Enter> for next field4) Continue adding rest of the Fields typing in “Plymouth” for the city, “MI” forthe state, “48170” for the zip, and “388888888”for the soc
![Page 41: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/41.jpg)
41
Simple Access Database: Manual Data Entry
• DoctorInformation data
• At theHire_date fieldEnter in 07/07/2009. TimeDefaults to noon
1) Click on the disk icon to save the table.
1) Right mouse click
2) Click Open1) <Tab> past doctor id field on to first name.2) Type first name “Gina”3) <Tab > to last_name enter “Smith” for last name4) Continue tabbing and entering in fields
![Page 42: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/42.jpg)
42
Simple Access Database: Manual Data Entry
• DoctorInformation data
• After enteringIn the hire_datehit <enter>. Results are asshown AND data is saved tothe table
1) Right mouse click
2) Click Open
![Page 43: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/43.jpg)
43
Simple Access Database: Manual Data Entry
• DoctorInformation data
• After enteringIn the hire_datehit <enter>. Results are asshown AND data is saved tothe table
1) Right mouse click
2) Click Open
![Page 44: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/44.jpg)
44
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Note, you can delete arecord by selecting arecord, right-clicking and choosing delete
1) Right mouse click
2) Click Open
![Page 45: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/45.jpg)
45
Simple Access Database: Manual Data Entry
• DoctorInformation data
• Confirmationdialog box popsUp. Click OK
1) Right mouse click
2) Click Open
![Page 46: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/46.jpg)
46
Simple Access Database: Manual Data Entry
• DoctorInformation data
• End resultis as shown
1) Right mouse click
2) Click Open
![Page 47: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/47.jpg)
47
Simple Access Database: Manual Data Entry (Exercise)
• Enter data into the patient_information table. Add a record with the following values (in order –
omitting double quotes):“ Mann”, “Bob”, “John”, “380004444”, “444 Lockwood”,“Novi”, “MI”, “48170”, “12/01/1970” Patient_id Patient_last_
namePatient_first_name
Patient_middle
Patient_soc Patient_address
Autonumber Text Text Text Number text
Patient_city Patient_state Patient_zip Date_of_birth
Text Text Number Date time
![Page 48: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/48.jpg)
48
Simple Access Database: Foreign Keys
Purpose:1) Creating Relationships2) Enforcing Integrity
By Creating Child and Parent tables you are creatingRELATIONSHIPS
![Page 49: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/49.jpg)
49
Simple Access Database: Foreign Keys: Creating Relationships
• Parent tablesAnd Child tables• Fields in Child Table (patientAppointment)referenceFields in Parent Tables (doctor _information,patient_information)• Patient_appointmentDoctor_ID and Patient_ID are foreign keys
Values in Patient_appointmentDoctor_ID mustExist in Doctor_informationDoctor_id
Values in Patient_appointment Patient_ID must exist in patient_informationPatient_id
![Page 50: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/50.jpg)
50
Simple Access Database: Creating Foreign Keys Creates Relationships: Bring Tables into ERD diagram
• Creating Child Tables is how youCREATE RELATIONSHIPS
• Creating Child tables• Go under DatabaseTools and click on Relationships for a List of tables to Bring into the ERD.
Click on relationships for a list of tables to bring into The ERD (Entity RelationshipDiagram)
![Page 51: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/51.jpg)
51
Simple Access Database: Creating Foreign Keys Creates Relationships
• Creating Child table• Next, • Click on the Doctor_information, • Hold the <SHIFT> key• Click on Patient_informationAnd Patient_appointment
![Page 52: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/52.jpg)
52
Simple Access Database: Creating Foreign Keys Creates Relationships
• Create childTable
• Click Add • Tables
Appear In the ERD
![Page 53: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/53.jpg)
53
Simple Access Database: Creating Foreign Keys Creates Relationships
• Create childTable
• Left mouseClick onPatient_appointmentMove it down sinceIt is the child table
![Page 54: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/54.jpg)
54
Simple Access Database: Foreign Keys
• Create childTable
• Double clickOn doctor_id Of doctor_informationTo bring up Relationship window
![Page 55: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/55.jpg)
55
Simple Access Database: Foreign Keys
• Create childTable
• Double clickOn doctor_id Of doctor_informationto bring up Relationship window
![Page 56: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/56.jpg)
56
Simple Access Database: Creating Foreign Keys Creates Relationships
• Create childTable Patient_Appointment
• Left tablename choose Doctor_information. Right tableName choose Patient_appointment
![Page 57: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/57.jpg)
57
Simple Access Database: Foreign Keys
• Create childTable
• Left columnname choose Doctor_id. Right columnName choose Doctor_id
![Page 58: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/58.jpg)
58
Simple Access Database: Foreign Keys
• Create childTable
• Click OK
![Page 59: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/59.jpg)
59
Simple Access Database: Foreign Keys
• Create childTable
• Click Create
![Page 60: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/60.jpg)
60
Simple Access Database: Foreign Keys
• Create childTable
• Relationshipis created
![Page 61: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/61.jpg)
61
Simple Access Database: Foreign Keys: Creating Relationship between patient_appointment and
patient_information
ChildTable:PatientAppointmentParent table:Patient Information
Repeat for Patient Information
1)1) Choose Patient_informationFor left table (parentTable)
2) Choose Patient_appointmentFor right table (child table)
2)
![Page 62: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/62.jpg)
62
Simple Access Database: Foreign Keys: Creating Relationship between patient_appointment and
patient_information
ChildTable:PatientAppointmentParent table:Patient Information
1)
1) Choose patient_idColumns frompatient_informationand patient_appointment tables
2)
![Page 63: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/63.jpg)
63
Simple Access Database: Foreign Keys: CreatingRelationships
• Create childTable
• Relationshipis created
Child
Parent
Relationship
You can double-clickOn the relationshipTo edit it later on
![Page 64: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/64.jpg)
64
Simple Access Database: Foreign Keys: Enforcing Integrity
• IntegrityIs enforced
Can only use existing doctor ids of 5 or 6 (in this example). Note your doctor ids may be different
![Page 65: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/65.jpg)
65
Data Entry Screens: Easy way to enter data
Purpose of Data Entry Screens: • To enter the data in a fast and efficient way• To keep data organized • We build data entry screens by choosing
tables and columns and then adding a buttonfor the data entry
• We create data entry screens for the parent Tables first and then the child tables
![Page 66: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/66.jpg)
66
Data Entry Screens: Parent Tables
• Create Data Entry Screens
• Doctor_Information table
1) Click on create2) Click on form design
![Page 67: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/67.jpg)
67
Data Entry Screens: Parent Tables
• Create Data Entry Screens
• Doctor_Information table
1) Click on Add ExistingFields
![Page 68: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/68.jpg)
68
Data Entry Screens: Parent Tables
• Create Data Entry Screens
• Doctor_Information table
1) Click on Add ExistingFields
![Page 69: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/69.jpg)
69
Data Entry Screens: Parent Tables
• Create Data Entry Screens
ExpandDoctor_Information table
Expand doctor_information
![Page 70: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/70.jpg)
70
Data Entry Screens: Parent Tables:Doctor_Information
• Create Data Entry Screens
Drag doctor id field to edge of second square
Drag first_name field to edge of second square
![Page 71: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/71.jpg)
71
Data Entry Screens: Parent Tables:Doctor_Information
• Create Data Entry Screens
Drag each field to edge of second square
Drag each field to edge of second square.Hire_date is the last field
![Page 72: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/72.jpg)
72
Data Entry Screens: Parent Tables: Doctor_Information: Creating button
to insert data
• Create Data Entry Screens
(2) Click on Button
(3) Click on RecordOperations
4) Click on Save Record
5) Click on Finish
(1) Click on Design Tab
(1)(2)
![Page 73: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/73.jpg)
73
Data Entry Screens: Parent Tables: Doctor_Information: OR much simpler way
to create a form
• Create Data Entry Screens
1) Click on doctor_Information table
2) Click on create
3) Click on Form NOT Form Design
(1) Click on doctor_information
(2) Click on Create
(3) Click on Form
![Page 74: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/74.jpg)
74
Data Entry Screens: Parent Tables: Doctor_Information: OR much simpler way
to create a form without dragging each field:
• Create Data Entry Screens
1) After clickingon form, a form is created automaticallyfor the‘doctor_information’Table.
![Page 75: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/75.jpg)
75
Data Entry Screens: Parent Tables: Doctor_Information: OR much simpler way
to create a form without dragging each field:
• Create Data Entry Screens
1) Click on the Save disk icon
(1) Click on Save disk icon
![Page 76: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/76.jpg)
76
Data Entry Screens: Parent Tables: Doctor_Information: OR much simpler way
to create a form without dragging each field:
• Create Data Entry Screens
1) Type inDoctor_information
2) Press OK. Doctor_informationIs saved as a form
(1) Click on Save disk icon
(1) Type in doctor_information(2) Press OK
![Page 77: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/77.jpg)
77
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Add buttonto Data EntryScreen
1) Right-mouseClick on the formDoctor_information
2) Click on Design View to Get into DesignView to add button.
(1) Right click on form doctor_information
(2) Click on Design View
![Page 78: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/78.jpg)
78
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Add buttonto Data EntryScreen
1) Click on the Button icon.
(1) Click on the button
![Page 79: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/79.jpg)
79
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Add buttonto Data EntryScreen
(1) Drag rectangle to the footer section and click
(1) Drag rectangle to the footer section and click
![Page 80: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/80.jpg)
80
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Choose action for button
(1) Click on Record Operations
(2) Click on Add New Record
(1) Click on Record Operations
(2) Click on Add New Record
(3) Click on Next(3) Click on Next
![Page 81: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/81.jpg)
81
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• NameButtonOn Data EntryScreen
(1) Click on Text
(2) Type Add Record
(3) Click on Next(3) Click on Next
(1) Click on Text(2) Type Add Record
![Page 82: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/82.jpg)
82
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• NameButtonOn Data EntryScreen
(1) Type Add Record
(2) Click on Finish
(1) Type Add Record
(2) Click on Finish
![Page 83: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/83.jpg)
83
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Runthe formto entera new record
(1) Double click on Doctor Information form
(1) Double click on Doctor Information form
![Page 84: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/84.jpg)
84
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Runthe formto entera new record
(1) Tab pastdoctor_idand enter In the followingFields
2) Press Add Recordto add the record
Press Add Recordto add the record
![Page 85: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/85.jpg)
85
Data Entry Screens: Parent Tables: Doctor_Information: Design mode: Add a button
• Runthe formto entera new record
(1) Record hasbeen added
![Page 86: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/86.jpg)
86
Data Entry Screens: Parent Tables: (Exercise)
• Add a data entry screen for Patient Information andadd a button to Add a record. BONUS: Add anotherButton to delete a record
![Page 87: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/87.jpg)
87
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Build formfrom patient_AppointmentTable
(1) Select patient_Appointment table(2) Choose create(3) Choose form
![Page 88: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/88.jpg)
88
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Build formfrom patient_AppointmentTable
(1) Form is createdFor patient_Appointment
2) Right clickOn patient_Appointment
3) Click on design view
![Page 89: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/89.jpg)
89
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Build dropDown listFor doctors
(1) Click on Drop down list
2) Drag it to the detail part of the form
3) Notice the dialog box comes up. Make sure first option is selected. Click Next
(1) Click on Drop down list
2) Drag it to the detail Part of the form
(3) Notice the dialog box comes up. Make sure the firstoption is selected.Click Next
![Page 90: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/90.jpg)
90
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Choose Source for Drop down listof doctors (1) Choose
Doctor_informationto populate the drop Down list .
(2) Click Next
(2) Click Next
(1) Choose Doctor_informationto populate the drop Down list .
![Page 91: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/91.jpg)
91
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Choose fields for Drop down listof doctors (1) Choose last_name.
Click >.
2) Choose first_name. Click >.
(3) Click Next
(1) Choose last_name.Click >.
2) Choose first_name. Click >.
3) Click Next
![Page 92: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/92.jpg)
92
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Choose Sorting for Drop downlist (1) Choose last_name.
(2) Click Next
(1) Choose last_name.
(2) Click Next
![Page 93: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/93.jpg)
93
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Hide theKey column
(1) Make sure hide keyColumn option is chosen
(2) Click Next
(1) Make sure hide key Column option is chosen.
(2) Click Next
![Page 94: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/94.jpg)
94
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• Store valueIn lookup list
(1) Make sure storevalue in field option is chosen
(2) Choose doctor_id From drop down
(3) Click finish
(1) Make sure store value in field option is chosen.
(2) Choose doctor_idFrom drop_down
(3) Click finish
![Page 95: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/95.jpg)
95
Data Entry Screens: Child Tables: Patient_Appointment with a look-up list of doctors
• DoctorLookup listcreated
(1) When You bring upThe patient_Appointment form(by double-clickingon this form) yousee the drop downof doctors.
![Page 96: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/96.jpg)
96
Data Entry Screens: Parent Tables: (Exercise)
• Add another drop down list on the patient_appointment form to show a drop down
of PATIENTS (from the patient_information table)
![Page 97: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/97.jpg)
97
Data Entry Screens: End Result
Have pull down for doctor and Patients
Have button to insert
![Page 98: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/98.jpg)
98
Data Reporting Purpose: Fields and grouping
• Data Reporting Purpose:1) Choose fields that you want to include on the report . We will be choosing fields fromPatient_appointment, Doctor_information, And Patient_information.2) Choose how you want to group your data
![Page 99: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/99.jpg)
99
Data Reporting: Creating Simple Data Reportswith the Reporting Wizard : fields
• CreateReports
(2) Click on (3) Report Wizard
(1) Click on Create
(1)(2)
![Page 100: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/100.jpg)
100
Data Reporting: Creating Simple Data Reportswith the Reporting Wizard : fields
• CreateReportsPatient_appointment fields
(1)(2)
1) Choose patient_appointment for the table2) Choose appt_date from left side and hit arrow
to move it to the right side.3) Choose appt_desc from left side and hit
Arrow (->) to move it to right side.
(1) Choose field
(2) Hit Arrow
![Page 101: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/101.jpg)
101
Data Reporting: Creating Simple Data Reportswith the Reporting Wizard : fields
• CreateReportsDoctor Information fields
(1)(2)
1) Choose Doctor_information for the table2) Choose first_name from left side and hit arrow
to move it to the right side.3) Choose last_name from left side and hit
Arrow (->) to move it to right side.
![Page 102: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/102.jpg)
102
Data Reporting: Creating Simple Data Reportswith the Reporting Wizard : fields
• CreateReportsPatient Information fields
(1)(2)
1) Choose Patient_Information for the table2) Choose Patient_first_name from left side and hit arrow
to move it to the right side.3) Choose patient_last_name from left side and hit
Arrow (->) to move it to right side.
![Page 103: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/103.jpg)
103
Data Reporting: Creating Simple Data Reportswith the Reporting Wizard : Grouping
• ChooseHow you Want to Group yourdata
Gives you flexibility in grouping data
Choose doctor_information
![Page 104: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/104.jpg)
104
Data Reporting: Hit Next and Finish. End Result:
Doctor NameEach Patient
![Page 105: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/105.jpg)
105
Data Reporting: Can also group report by Patient Information
PatientEach Doctor Appointment
![Page 106: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/106.jpg)
117
Conclusion
MS Access is an easy and efficient way to store Data and enables you to build Tables, Data Entry Screens, Reports, and Applications.
![Page 107: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/107.jpg)
Questions
118
![Page 108: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/108.jpg)
119
ResourcesTraining from CSCAR
http://www.umich.edu/~cscar/
• Statistical Analysis with R -- October 20, 9am-5pm• Intermediate SAS -- October 25, 27 + 29, 9am-12:30pm• Using ArcGIS -- October 26 + 28, 9am-5pm• Applied Structural Equation Modeling -- November 1, 2 + 3, 9am-5pm• Introduction to NVivo -- November 9 + 11, 9am-12:30pm• Applications of Hierarchical Linear Models -- November 8, 10 + 12• Introduction to Programming in Stata -- November 9, 11, 16 + 18, 1pm-4:30pm• Regression Analysis -- November 17 + 19• Classification and Regression Trees Using JMP -- December 2 + 3
![Page 109: Ann Arbor ASA ‘Up and Running’ Series: ACCESS](https://reader035.vdocuments.us/reader035/viewer/2022062816/5681636e550346895dd44a49/html5/thumbnails/109.jpg)
120
Other Workshopsfrom Ann Arbor ASA
• Stata
• Statistics with Excel
• R
• MS Access
• SAS