Transcript Congrats, you`re teaching Access!

Hooking Up with Access
Lisa Friedrichsen, Illustrated Access Author
Johnson County Community College Professor
Congrats, you’re teaching Access!
 What approach will you take?
o User’s perspective?
o Developer’s perspective?
o AND, at what point do you teach relational design?
Congrats, you’re teaching Access!
 Access, from a user’s perspective
o
o
o
o
Working with data
Using forms
Using reports
Building queries
Congrats, you’re teaching Access!
 Access, from a developer’s perspective
o
o
o
o
o
o
Building tables
Building queries
Building forms
Building reports
Building macros
Perhaps creating VBA modules
Congrats, you’re teaching Access!
 But what about database design?
 Normalization?
 Relationships?
 Referential integrity?
 A properly designed database is the key to success!
xkcd.com
xkcd.com
xkcd.com
Congrats, you’re teaching Access!
 So how do we teach relational design in a way that students
can actually learn it?
 Normalization
 Third Normal Form
 Entity Relationship Diagrams
 (Been there, done that)
xkcd.com
xkcd.com
xkcd.com
xkcd.com
So how do you teach Access? (and all that
goes with a solid relational design)
By Example!
Show limitations of an easy, one-table
(Excel) solution
 Analysis (sort, filter, find, calculations)
o How many students do we have? (can’t answer)
o How many courses do we have? (can’t answer)
 Data entry is clunky
 Reporting is almost nonexistent
 Single user
 Data integrity issues
Show benefits of a relational database
solution (Access)
 Analysis (sort, filter, find, calculations) = queries
o We know how many students we have
o We know how many courses we have
 Data entry = forms
 Reporting = reports
 Inherently multi-user
 Data integrity is solid!
Getting from here to there (normalization)
 Step 1: Create a perfect single table of data in Excel
 Rules:
o
o
o
o
o
1st row field names
2nd and subsequent rows of data
No blank rows or columns
No total rows
No formulas
Getting from here to there (normalization)
 Step 2: Import the data in a blank Access database
 Rules:
o If errors, delete the imported table and fix the data in Excel
o An “Errors” table will tell you which rows have errors
o Name the imported table, ImportedData
Getting from here to there (normalization)
 Step 3: Break ImportedData into subject tables
 Rules:
o Think about subjects
 Each record must be one of that subject
 Each field must describe that one record
o Build make-table query to create subject tables
 Summary query to extract unique records
Getting from here to there (normalization)
 Step 4: Set primary key field in new tables
 Primary key uniquely identifies each record in the table
 Rules:
o Set natural primary key fields first
o If no “natural” primary key field, add an AutoNumber field
Getting from here to there (normalization)
 Step 5: Relate tables
o Ask yourself how ONE record relates to MANY records
 Rules:
o Primary key field is always on “one” side (parent)
o Linking field is called “foreign key field” on “many” side (child)
o If you have trouble at this step, go back to Step 3 (think)!
Getting from here to there (normalization)
 Step 6: Be sure to enforce referential integrity
o Prevents orphans!
 Children that have no parent
 Definition: Records in the “many” table that do not have a match
in the “one” table
 Examples:
 Enrollments (child) into a class (parent) that doesn’t exist
 Enrollments (child) for a student (parent) that doesn’t exit
Getting from here to there (normalization)
 Step 7: Build a query that matches the ImportedData table
 Then, have some fun:
o Change values in both examples
o Explain that the changes populate all queries, forms, and reports
o Explain that data is stored only once in tables, but SELECTed several
times due to relationships
o The relationships are the key to data integrity
 A student is stored only once!
 A course is stored only once!
 (Repeated data in one table is the enemy.)
Getting from here to there (normalization)
 Step 8: Practice, practice, practice
o Need More Examples?
o Email:
[email protected]
Hooking up with Access
 THANK YOU and best wishes!
 Lisa Friedrichsen
o Access author, Illustrated series
o CIS Professor
@ Johnson County Community College in Overland Park KS
 [email protected]