Learn Microsoft Access Advanced

Microsoft Access

Advanced

The Instructors  Allyson

Mower, Digital Initiatives, 581-5263, [email protected]  Alice Weber, Collection Development, 587-9247, [email protected]  Joan Gregory, Technical Services, 581-5269, [email protected]

Your Expectations/Experience?

Class Objectives  Overview

of some basic database design

principles  Create a sample MS Access database from scratch  Modify database objects  Learn how to import data from other programs

Definitions  MS

Access - software used for creating databases  Data are just information  Database

• Collection of data/information • Related to a particular topic or project

Basic Database Design Principles  Avoid

Duplication

• Tables, fields, records

 Enforce

Data Integrity

• Data are referenced throughout the entire

database so that changes made in one table affect other tables

 Ensure

Data Accuracy

• Formats (mm-dd-yyyy), pick lists, controlled vocabulary

Main Parts of Database Design  Mission

Statement

 Tables  Fields  Identifying

Relationships  Enforcing Integrity

Sample Database Scenario A VERY simple example A small business owner with three staff members needs to contact customers by phone.

Mission Statement A

mission statement clearly explains the purpose of the database  Sample mission statement: The contact management database will keep track of customers, the calls made, and by whom.

Mission Statement >>Tables  The

mission statement is used to develop a list of tables. “The contact management database will keep track of customers, the calls made, and by whom.”

 Each

table represents a single subject, object or event.  Avoid duplication

Tables  Customers

• “Keep track of customers”

 Calls

• “the calls made”

 Employees

• “and by whom”

Tables >>Fields  Tables

are made up of fields that represent a characteristic of the subject, object or event  For example, each person has:

• First name • Last name • Phone number

 Avoid

duplication by specific naming

Fields in the Calls Table 

These fields represent characteristics of the Calls:

• • • • • • •

Call ID Customer ID Employee ID Call Date Call Time Call Subject Call Notes

Required Fields  Each

table must have a Primary Key field

• to uniquely identify a record • to establish relationships between tables • Typically, it is an auto-generated number

 Foreign

Key fields are required only:

• to facilitate relationships between tables • to enforce data integrity

Fields in the Calls Table: Primary Key 

These fields represent characteristics of the Calls:

►Call ID • Customer ID • Employee ID • Call Date • Call Time • Call Subject • Call Notes

Fields in the Calls Table: Foreign Keys 

These fields represent characteristics of the Calls Table:

• Call ID ►Customer ID ►Employee ID • Call Date • Call Time • Call Subject • Call Notes

Main Parts of Database Design  Mission

Statement

 Tables  Fields  Identifying

Relationships  Enforcing Integrity

What You Don’t Know – Can Cause You Problems  Database

design requires: • Thinking through what you want and need out of your database • Assessing whether or not you have the skills to create that database • Recognizing when you need database programming expertise

MS Access and its Templates Can Help You Test your database design ideas  Determine if your database is simple or complex  Create a simple database from scratch  Create a more complex database using templates  Identify when you need a database programmer  Help you explain what you want to a database programmer, so the results meet your needs 

Questions

Now for the Hands-On Practice!

MS Access Tutorial