Microsoft Access Lesson 3

1
Microsoft Access Lesson 3

• Lexington Technology Center
• February 18, 2003
• Bob Herring
• On the Web at www.lexington1.net/AdultEd/computer/
  microsoft_access.htm

2
Access Lesson 3
Review of Thursdays Lesson

• Template Databases
• Database Wizard
• Switchboard Forms
• Forms and Controls
• Reports
• Database Window

2
3
Access Lesson 3
Relationships

• Tables can be related if they have a field in
  common. Access uses the
• relationship to create information by
  matching the fields and reporting the
• results.
• There are 3 types of relationships
• One-to-many Each record in the primary table
  can be associated with
• many records in the
  related table
• One-to-one Each record in the primary table
  can be associated with
• only one record in
  the related table
• Many-to-many More than one record in the
  primary table can be
• associated with
  many records in the related table

3
4
Access Lesson 3
Relationships in Access

• The table on the one side of the relationship
  is called the parent table.
• The other table is called the child table.
• How the 3 types of relationships are created in
  Access
• One-to-many The common field in the parent
  table is called the
• primary key, and
  the related field in the child table is
• called the foreign
  key.
• One-to-one The common fields are both primary
  keys.
• Many-to-many Ordinarily not possible, but in
  Access two one-to-
• many
  relationships can be joined to create this kind
• of
  relationship.

4
5
Access Lesson 3
One-to-Many Relationships

• In this example, one category can encompass
  many products

Child Table
Foreign Key
Parent Table
Primary Key
Relationship Line
5
6
Access Lesson 3
One-to-One Relationships

• These two tables have a one-to-one relationship
  because they
• concern the same subject employees

6
7
Access Lesson 3
Many-to-Many Relationships

• Since many orders might call for many products,
  a junction table
• is used to avoid violating referential
  integrity.

Junction Table
7
8
Access Lesson 3
Referential Integrity

• Optional series of rules that guarantees that
  relationships will be valid.
• Every record in a child table (the many side)
  must have one and
• only one record in the parent table (the
  one side)
• No child record can be entered without a parent
• No parent record can be deleted if it still has
  children
• No child records foreign key can be changed
  without a match in
• the parent table
• No parent tables primary key can be changed if
  there are child
• records

8
9
Access Lesson 3
Referential Integrity, Continued

• What you need to enforce referential integrity
  in Access
• The matching field in the parent table must be
  the primary key
• Relating fields must be the same data type
• Both tables must be in the same database -- OR,
  if using linking
• fields, both tables must be in Access and
  the linked tables
• database must be open
• Referential integrity is indicated by 1 - ? on
  the relationship line

Referential Integrity Enforced
9
10
Access Lesson 3
Referential Integrity, Continued

• Referential integrity is set by editing the
  relationship
• Right-click the relationship line and select
  Edit Relationship
• Click the Enforce Referential Integrity check
  box

Enforce Referential Integrity
Edit Relationship
10
11
Access Lesson 3
Referential Integrity, Continued

• If referential integrity is selected, you can
  also choose to have Access
• automatically update or delete records
• Cascade Update and Cascade Delete maintain
  referential integrity by
• changing child tables to match changes in
  the parent table
• Beware of Cascade Delete!! This could lead to
  loss of data!
• Cascade Update has no effect if you are using a
  primary key since
• you cannot change an autonumber

Cascade Update
Cascade Delete
11
12
Access Lesson 3
Joins

• A join is a control that tells Access which
  records to select when
• running a query.
• Inner joins are the most common.
• Outer joins include all records from one of the
  tables and records
• from the other when the joined fields
  match.

Left
Right
Inner Join
Left Outer Join
Right Outer Join
12
13
Access Lesson 3
Address Book Relationships
13
14
Access Lesson 3
Asset Tracking Relationships
14
15
Access Lesson 3
Contact Management Relationships
15
16
Access Lesson 3
Event Management Relationships
16
17
Access Lesson 3
Expenses Relationships
17
18
Access Lesson 3
Students and Classes Relationships
18
19
Access Lesson 3
Inventory Control Relationships
19
20
Access Lesson 3
Ledger Relationships
20
21
Access Lesson 3
Resource Scheduling Relationships
21
22
Access Lesson 3
Time and Billing Relationships
22
23
Access Lesson 3
Membership Relationships
23
24
Access Lesson 3
Order Entry Relationships
24
25
Access Lesson 3
Book Collection Relationships
25
26
Access Lesson 3
Music Collection Relationships
26
27
Access Lesson 3
Form Wizard

• In the Northwind database, select the Forms
  tab
• Click the New button

27
28
Access Lesson 3
Form Wizard

• In the New Form dialog box, click Form
  Wizard
• Choose the Products table to begin, then click
  OK

28
29
Access Lesson 3
Wizard, Step 2 -- Data Fields

• Step 2 is to choose fields from the available
  tables and queries
• Click the right arrow to select the desired
  fields, or
• Click the double right arrow to select all

29
30
Access Lesson 3
Choosing Fields for a Form

• Select ProductName, SupplierID,
  UnitsinStock,
• and Discontinued
• Choose Next

30
31
Access Lesson 3
Wizard, Step 3 -- Layout Style

• In Step 3, select a layout style for the form
• Click through the four choices to preview the
  different styles
• Click Next

Columnar
Tabular
31
32
Access Lesson 3
Wizard, Step 3 -- Layout Style

• In Step 3, select a layout style for the form
• Click through the four choices to preview the
  different styles
• Click Next

Datasheet
Justified
32
33
Access Lesson 3
Wizard, Step 4 -- Background Style

• In Step 4, select a background style for the
  form
• Click through the choices to preview the
  different styles

33
34
Access Lesson 3
Wizard, Step 5 -- Finish

• To finish the form, give it a name and click
  Finish
• Click the radio button to open the form after
  creating it

Open the Form
34
35
Access Lesson 3
Automatic Form in Action

• The new form displays a portion of the
  information in the table
• Forms are two-way -- they display information,
  but also guide
• data entry

35
36
Access Lesson 3
Adding a New Product

• Click the New Record button
• Notice that the Supplier field is lit -- this
  is referential integrity
• in action -- every product must have a
  supplier!

36
37
Access Lesson 3
Adding a New Product

• After choosing a supplier, enter the name of
  the new product
• Enter the number of units in stock
• Data entered in the form is immediately saved
  in the database

37
38
Access Lesson 3
Review

• Relationships
• Referential Integrity
• Joins
• Template Database Relationships
• Automatic Forms

38