Exploring Microsoft Access 2003

1
Exploring Microsoft Access 2003

• Chapter 4 Proficiency
• Relational Databases, External Data,
• Charts, Pivot, and the Switchboard

2
Objectives (1 of 2)

• Describe one-to-many relationships
• Create a one-to-many relationship
• Use the Get External Data command
• to get data from Microsoft Office Excel
• Create and modify a multiple-table selectquery.

3
Objectives (2 of 2)

• Use aggregate functions to create a totals query
• Use Microsoft graph to create a chart based on a
  table or query
• Use the Switchboard Manager to create and/or
  modify a switchboard

4
Overview

• Share data between Microsoft Office applications
• Display data from two tables in one query
• Total query aggregates results from groups
• of records to create summary information
• Create Chart and Pivot
• Create a user interface

5
Multiple-Table Queries

• One-to-many relationship
• Primary key (PK)
• Foreign key (FK)
• i) Consider two relation schemas R1 and R2
• ii) The attributes in FK in R1 have the same
  domain(s) as the primary key attributes PK in
  R2 the attributes FK are said to reference or
  refer to the relation R2.

6

• Referential integrity
• A value of FK in a tuple (record) t1 of the
  current state r(R1) either occurs as a value of
  PK for some tuple t2 in the current state r(R2)
  or is null. In the former case, we have t1FK
  t2PK, and we say that the tuple t1 references
  or refers to the tuple t2.

FK
Example
Employee(SSN, , Dno)
Dept(Dno, )
7
Get External Data

• Get External Data command
• Export command
• Import Spreadsheet Wizard
• Import Text Wizard
• Importing versus linking

8
Importing Exporting

• Hands-On Exercise 1
• Open the Investment Database
• Import Spreadsheet Wizard
• Create the Relationship
• Print the Relationship
• Add the New Data
• Create Complete the Multiple-Table Query
• Export the Query and Modified Tables
• View the Excel Workbook

9
Multiple Table Query
Relationshipbetween tables
Each field table to display
10
Total Queries

• A total query
• Summary functions
• Total row
• Group By
• Count function
• Sum function

11
Total Queries Charts

• Hands-On Exercise 2
• Copy Assets Under Management Query
• Create a Total Query
• Check Your Progress
• Start the Chart Wizard
• Complete the Chart Wizard
• Increase the Plot Area
• Change the Data

12
Total Query
Run button
Select Count from drop-down menu
13
SELECT Consultants.Lastname, Count(Clients.LastNam
e), Sum(Assets) FROM Consultants, Clients WHERE
Consultant.ConsultantID Clients.ConsultantID GRO
UP BY Consultants.Lastname
14
(No Transcript)
15
SELECT Consultants.Lastname, Consultants.Status,
Clients.Lastname, Clients.CountType,
Clients.Assets FROM Consultants, Clients WHERE
Consultant.ConsultantID Clients.ConsultantID GRO
UP BY Consultants.Lastname
16
4
5
1
5
4
The results are grouped according to the last
name of the consultants. The records with the
same last name are in the same group.
17
Referential Integrity
Delete Record button
Click to display related records
You cannot delete a Consultant without first
deleting related Clients
18
EMPLOYEE
fname, minit, lname, ssn, bdate, address, sex,
salary, superssn, dno
DEPARTMENT
Dname, dnumber, mgrssn, mgrstartdate
Dnumber, dlocation
PROJECT
DEPT _LOCATIONS
Pname, pnumber, plocation, dnum
WORKS_ON
Essn, pno, hours
DEPENDENT
Essn, dependentname, sex, bdate, relationship
19

• Updating and constraints
• delete
• Delete the WORK_ON tuple with Essn 999887777
  and pno 10.
• When deleting, the referential constraint will be
  checked.
• - The following deletion is not acceptable
• Delete the EMPLOYEE tuple with ssn
  999887777
• - reject, cascade, modify (cascade update)

20
Cascade delete a strategy to enforce
referential integrity
Employee
Works-on
21
Cascade delete a strategy to enforce
referential integrity
Employee
not reasonable
22
(No Transcript)
23
Modify a strategy to enforce referential
integrity
Employee
delete
This does not violate the entity constraint.
24
Chart Wizard
View button
Modified Y and X axis now match the query data
Chart from Wizard
25
The User Interface

• Switchboard
• Switchboard Manager
• Switchboard Items table

26
Other Access Utilities

• Convert Database command
• Compact and Repair Database command

27
Compact the Database
View button
Select the database
Click compact
28
The Switchboard Manager

• Hands-On Exercise 3
• Start the Switchboard Manager
• Complete the Switchboard
• Test the Switchboard
• Insert the Clip Art
• Complete the Design
• The Completed Switchboard
• Compact the Database

29
Switchboard Manager
Each button corresponds to a command
30
Chapter 4 Summary (1 of 2)

• One-to-many relationships
• A query can display data from multipletables
• Get External Data command
• A total query performs calculations on
• a group of records using summaryfunctions

31
Chapter 4 Summary (2 of 2)

• Switchboard Manager creates the userinterface
  (Switchboard)
• Convert Database command changes anAccess 2000
  file to a previous version
• Compact and Repair Database command

32
Practice with Access

• 1. The Client Master List
• 2. The HMO Database
• 3. Creating a Switchboard
• 4. The Look Ahead Databas
• 5. Linking Versus Importing
• 6. Pivot Tables
• 7. Pivot Charts

33
Case Studies

• Your First Consultants Job
• The Wellness Center
• The Database Wizard
• Compacting Versus Compressing