January 12, 2004

1
Microsoft Access Workshop

• January 12, 2004
• Instructor
• Susan St. John
• Labrats
• Donna Cook, Sean Lanksbury, Lisa Mendez

2
Acknowledgments

• Eva Piccininni
• Liisa Rogers
• Grace Whiteaker
• Contributed to previous versions of this workshop

3
Logistics

• 10am-noon
• Two handouts slides, evaluation form
• 10 minute break somewhere near the middle
• Fill out evaluation form at end

4
Overview

• Why use Access (instead of Word or Excel)?
• In Access we will all
• Create tables, attributes, types
• Set up relationships between tables
• Create a query
• If we still have time, I will demo
• Forms
• Reports

5
Why (Relational) Databases?

• Unstructured information
• E.g. an essay
• Structured information
• E.g. fill out a form
• If spreadsheets are the number crunchers of
  the digital world, databases are the real
  information crunchers. Databases excel at
  managing and manipulating structured
  information.
• Rose Vines
• GeekGirls.com

6
Whats kept in databases?

• Your address book
• Names, phone numbers, addresses
• Library catalogues or bookstores
• Title, author, subject, availability, cover/page
  images
• Business
• Employee information, parts inventories
• Accounting and financial data
• University of Washington
• Student grades, tuition payments, classes offered
• Hospital
• Medical records (including images), insurance
  information

7
Informal tables (like Excel spreadsheets)

• Redundant data leads to problems
• errors
• update difficulties

8
One entity one table

• Create one table for each type of object or
  entity
• For the above informal table, each of the
  following would be given its own table
• Student table one entry (row) for each student
• Advisor table one entry (row) for each advisor
• Each row has a unique identifier called a primary
  key
• Primary key is never null and must be unique
  within the table
• This process of converting informal tables into
  relational database tables is referred to as
  normalization
• minimize duplication
• prevent inconsistencies

9
Parts of a Database Table
Attribute (Field) (Column)
Table Name
tblStudent
The primary key is a field that is unique for
each row. Here, it is the StudentID field
Record (Row) (Tuple)

• Attributes have types
• StudentID is an Number, Name is a Text string

10
Keys

• Used to create relationships between tables

tblStudent
tblAdvisor

• Note No data redundancies!

11
Lets get started!

• Questions so far?
• Start up Microsoft Access
• Create a new blank database and name it
  accessclass.mdb
• In your H drive folder
• Or, if not iSchool student, create a temp folder
  on the C drive
• Create a table in design view

12
Enter attributes and types
13
Primary key - AutoNumber

• Save, and name it tblStudent

14
Putting info into the table (1)

• Switch from Design view to Datasheet view
• either use View menu
• or the icon in the top left

15
Putting info into the table (2)

• Enter just a couple students for now
• Well have to come back later to fill in
  AdvisorID values
• Dont need to worry about saving! All data
  changes are immediate.

16
Create a second table, tblAdvisor

• Go back to the Database window
• Create table in design view again
• Create the attributes show below
• Make AdvisorID the primary key

• Save, and name it tblAdvisor

17
Fill in Advisor information

• Switch from Design view to Datasheet view
• Enter advisor information (as above)
• Now we have AdvisorID values to add into
  tblStudents

18
Primary and foreign keys

• tblAdvisors primary key (AdvisorID) is a foreign
  key in tblStudent
• Same field names are not required, but I advise
• Open tblStudent and add AdvisorID values

19
Table relationships (1)

• But theres an easier way!
• Close individual table windows
• Open the Relationships window
• Tools Relationships
• Add both tables into Relationships window

20
Table relationships (2)

• Join tblStudentAdvisorID to tblAdvisorAdvisorID
  (drag and drop)
• Enforce Referential Integrity (more on next
  slide)
• Save (relationships changes)

21
Referential Integrity

• Dont allow tables to get out of sync
• What should happen when you try to delete an
  Advisor who still has Students associated with
  them in tblStudent?

22
Relationship types

• One-to-One
• e.g. one student name, one library bar code
• Could put in same table, but sometimes reasons
  not to (e.g. separate institutions)
• One-to-Many
• e.g. one advisor has many students
• Many-to-Many
• e.g. each student is in many classes, each
  class contains many students
• need a bridge table to create this relationship

23
Add more records to tblStudents

• Open tblAdvisor
• Expand the view of the first record to show the
  joined table, tblStudent
• Enter more students (make up names) for each of
  the advisors
• Pretty cool, huh?

24
Tables more examples

• Again, avoid redundancy dont enter same info in
  more than one place
• Tables with pairs of foreign keys are called
  bridge tables

25
Bridge tables
26
Queries

• So far weve put info into tables in a database
• Queries are how you get the particular info you
  want out of a database
• E.g. Show all of Grace Whiteakers advisees

27
Creating a query (1)

• Switch to the Queries tab of the Database window

28
Creating a query (2)

• Create query in Design view
• Add both tables to the query window
• Drag and drop fields (attributes) from the tables
  to the query columns
• What fields do we need in order to
• Show all of Grace Whiteakers
  advisees?
• We arent done yet, but switch to Datasheet view
  to see what we have so far

29
Creating a query (3)

• Save, name your query qryWhiteakersStudents
• But we only want to see the students who have
  Whiteaker as their advisor

30
Creating a query (4)

• Add a criterion
• Decide which fields to show
• Specify a sort order

31
SQL (1)

• Structured Query Language
• A standard language for talking to relational
  databases (Microsoft Access and others)
• Weve seen Design View and Datasheet View now
  try SQL View
• use the View menu or the icon under File

32
SQL (2)

• SELECT
• Which fields to show
• FROM
• What tables are we using?
• JOIN has to do with the relationships between the
  tables
• WHERE
• Particular criteria
• ORDER BY
• Field(s) to sort on

33
Types of queries

• Select queries what we created above
• Action queries
• Update query
• Delete query
• Append query
• Make-Table query (helpful when exporting to
  another db, archiving old records)
• Crosstab query

34
Forms

• For displaying, entering, or modifying
  information
• Can create using
• Design view (essentially, by hand)
• Wizard
• AutoForm (time for demo?)

35
Reports

• For presenting database information in a printed
  format
• Can create using
• Design view (essentially, by hand)
• AutoForm
• Wizard (demo?)

36
Review

• Why use Access or other database program?
• One entity one table no redundancy
• Relationships primary keys, foreign keys
• Queries how to ask questions ( make changes)
• Forms for displaying, entering, editing
• Reports for presenting in a printed format
  (could also be an online summary)
• Questions?

37
A few online resources

• Geek Girls Database Tutorials
• http//www.geekgirls.com/menu_databases.htm
• FunctionXs Access Tutorial
• http//www.functionx.com/access/
• Find Tutorials (The Tutorials Search Engine)
• http//www.findtutorials.com/
• List of database tutorials
• http//www.katsueydesignworks.com/tutorials_databa
  ses.htm

38
Microsoft Access Workshop

• Were done!

Please fill out an evaluation form. And/or email
comments to sstjohn_at_u.washington.edu or
asis_at_u.washington.eduThank you!