Ann Arbor ASA ‘Up and Running’ Series: ACCESS

1. 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

2. PURPOSE: • INEFFICIENCIES of Data Storage in Excel • ADVANTAGES ofData Storage in Access • Simple Access Database: • Creation of tables: • doctor_information, • patient_information, • patient_appointment • Manual Data Entry • Foreign Keys • Data Entry Screens and Data Reporting

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

4. 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 Inefficiencies of Excel (continued) Inconsistent entries P Black. ,Dr. Peg Black. PREVENT accurate data tracking Leaving out an entry Dr. Peg Black PREVENTS accurate data tracking

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

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

7. ADVANTAGES of Database Storage in Access: Reports • Reports enable you to group Look up list Of Patient Names Each Patient Doctor Name

8. ADVANTAGES of Database Storage in Access: Applications • Access Data Entry Screens and Data Access Reports can be linked together to create Access Applications

9. Access Database:First Access Database Screen • Table:Spreadsheet • Rows of data • Columns of data • Database: Workbook • Collection of Spreadsheets or Tables

10. Simple Access Database • To start MS Access Under the Start menu Click on All Programs (Note, items on right side may be different) Click on All Programs

11. Simple Access Database • Under General Office Applications , Microsoft Office 2007 folders Click on Microsoft Office Access 2007 Click on Microsoft Office Access 2007

12. Simple Access Database • Click On Blank Database Click on Blank Database

13. Simple Access Database • Type in the database name (or file name) “database1” in the File_Name Text box. • Click Create button • Notice that the directory structure of the database appears above the create and cancel buttons Type in “database1” Directory Structure Click on Create button

14. Simple Access Database • This screen appears When you click the Create button. • At this point you are ready to add fields to your first table. Later on we will save this table and give it a name We will give this table a name later on We are adding fields to our new table

15. Simple Access Database (Table Creation) • Purpose to Create tables doctor_information, Patient_information, and patient_appointment. • Add columns, rename columns, and manually enter data.

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

17. Simple Access Database • Create Doctor_id Column by renaming ID field. Right mouse click. Choose rename column.

18. Simple Access Database (Move to next field) • Rename field Type in “doctor_id”. Double click on “add new field”

19. Simple Access Database (Move to next field) • Move to next field Type “first_name”. Hit <enter>

20. Simple Access Database (Move to next field) • Move to next field Blank field appears. Cursor is on the blank field Cursor is on blank field

21. Simple Access Database (Move to next field) • Add next fields Add next fields last_name, address, city, state in the same fashion. Type each field name. Hit <enter>

22. Simple Access Database (Move to next field) • Add next fields After adding last Field , state, and hitting <enter> Results are as follows: Cursor is here

23. Simple Access Database (Move to next field) • Modify data type Add numeric field Zip. Type in zip Type in Zip

24. Simple Access Database (Move to next field) • Modify data type Next, Click on white space. Click on Data Type List box and choose number Notice “add New field “ label For next field (1) Click on white space (2) Click on DataType list box and choose number

25. Simple Access Database (Move to next field) • Modify data type • Double Click on “Add new Field” To the right of “zip” • Repeat For the soc number. The resulting screen Looks as follows:  (1) Click on white space (2) Click on DataType list box and choose number

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 DataType list box and choose number

27. Simple Access Database (Save Table) • Save Table • Click on Upper-left corner disk icon • Type doctor_information in dialog box. 1) Click on the disk icon to save the table. 2) Type in doctor_information in dialog box

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

29. Simple Access Database (Create table for excercises) • To create the Tables for the Following exercises Click on Create Click on Create

30. Simple Access Database (Create table for excercises) • Next click On Table Click on Table

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

32. Simple Access Database (Exercise) • Create tables patient_information and patient_appointment table. The patient_information table has the following layout:

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 followi

34. Simple Access Database: Manual Data Entry • Doctor Information data • Open up doctor Information table to enter data • You can right mouse click on a table on the left side and click open 1) Click on the disk icon to save the table.

35. Simple Access Database: Manual Data Entry • Doctor Information data • Open up doctor Information table to enter data Right mouse click Click Open 1) Click on the disk icon to save the table.

36. Simple Access Database: Manual Data Entry • Doctor Information data • OR double click on doctor_information : Table Double click on doctor_information table Right mouse click Click Open 1) Click on the disk icon to save the table.

37. Simple Access Database: Manual Data Entry • Doctor Information data • Data Entry Screen Appears Right mouse click Click Open <Tab> past doctor id field on to first name. Type first name “Gina” <Tab > to last_name enter “Smith” for last name Continue tabbing and entering in fields Cursor is here 1) Click on the disk icon to save the table.

38. Simple Access Database: Manual Data Entry • Doctor Information data • Data Entry Screen Appears • Doctor_id filled In automatically by pressing <tab> Or <enter> Note: the Doctor_id shown on this slide may be different than what you have since the ids are automatically generated. • <Tab> past doctor id field on to first name. • Cursor is here • As soon as you start typing on • the first_name field … • the doctor_id field is populated

39. Simple Access Database: Manual Data Entry • Doctor Information data • Data Entry Screen Appears • Continue Tabbing and Entering fields Right mouse click Click Open <Tab> past doctor id field on to first name. Type first name “Gina” <Tab > to last_name enter “Anderson” for last name 4) Continue tabbing and entering in fields 1) Click on the disk icon to save the table.

40. Simple Access Database: Manual Data Entry • Doctor Information data • Tab to each field • Enter value • Hit <Tab> or <Enter> to move To next field Cursor is at address Type “123 Lane” for address <Tab> or <Enter> for next field Continue adding rest of the Fields typing in “Plymouth” for the city, “MI” for the state, “48170” for the zip, and “388888888” for the soc

41. Simple Access Database: Manual Data Entry • Doctor Information data • At the Hire_date field Enter in 07/07/2009. Time Defaults to noon Right mouse click Click Open <Tab> past doctor id field on to first name. Type first name “Gina” <Tab > to last_name enter “Smith” for last name Continue tabbing and entering in fields 1) Click on the disk icon to save the table.

42. Simple Access Database: Manual Data Entry • Doctor Information data • After entering In the hire_date hit <enter>. Results are as shown AND data is saved to the table Right mouse click Click Open

43. Simple Access Database: Manual Data Entry • Doctor Information data • After entering In the hire_date hit <enter>. Results are as shown AND data is saved to the table Right mouse click Click Open

44. Simple Access Database: Manual Data Entry • Doctor Information data • Note, you can delete a record by selecting a record, right- clicking and choosing delete Right mouse click Click Open

45. Simple Access Database: Manual Data Entry • Doctor Information data • Confirmation dialog box pops Up. Click OK Right mouse click Click Open

46. Simple Access Database: Manual Data Entry • Doctor Information data • End result is as shown Right mouse click Click Open

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”

48. Simple Access Database: Foreign Keys Purpose: • Creating Relationships • Enforcing Integrity By Creating Child and Parent tables you are creating RELATIONSHIPS

49. Simple Access Database: Foreign Keys: Creating Relationships • Parent tables And Child tables • Fields in Child Table (patient Appointment) reference Fields in Parent Tables (doctor _ information, patient_information) • Patient_appointment Doctor_IDand Patient_ID are foreign keys Values in Patient_appointment Doctor_ID must Exist in Doctor_information Doctor_id Values in Patient_appointmentPatient_IDmust exist in patient_information Patient_id

50. Simple Access Database: Creating Foreign Keys Creates Relationships: Bring Tables into ERD diagram • Creating Child Tables is how you CREATE RELATIONSHIPS • Creating Child tables • Go under Database Tools 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 Relationship Diagram)