exploring microsoft access 2003
DESCRIPTION
Exploring Microsoft Access 2003. Chapter 4 Proficiency: Relational Databases, External Data, Charts, Pivot, and the Switchboard. Objectives (1 of 2). Describe one-to-many relationships Create a one-to-many relationship Use the Get External Data command - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/1.jpg)
Exploring Microsoft Access 2003
Chapter 4 Proficiency:
Relational Databases, External Data,
Charts, Pivot, and the Switchboard
![Page 2: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/2.jpg)
Objectives (1 of 2)
• Describe one-to-many relationships
• Create a one-to-many relationship
• Use the Get External Data commandto get data from Microsoft Office Excel
• Create and modify a multiple-table selectquery.
![Page 3: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/3.jpg)
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
![Page 4: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/4.jpg)
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
![Page 5: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/5.jpg)
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.
![Page 6: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/6.jpg)
• 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 t1[FK] = t2[PK], and we say that the tuple t1 references or refers to the tuple t2.
Example:
Employee(SSN, …, Dno) Dept(Dno, … )
FK
![Page 7: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/7.jpg)
Get External Data
• Get External Data command
• Export command
• Import Spreadsheet Wizard
• Import Text Wizard
• Importing versus linking
![Page 8: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/8.jpg)
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
![Page 9: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/9.jpg)
Multiple Table Query
Relationshipbetween tables
Each field &table to display
![Page 10: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/10.jpg)
Total Queries
A total query
• Summary functions• Total row• Group By• Count function• Sum function
![Page 11: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/11.jpg)
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
![Page 12: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/12.jpg)
Total Query
Run button
Select Count fromdrop-down menu
![Page 13: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/13.jpg)
SELECT Consultants.Lastname, Count(Clients.LastName), Sum(Assets)FROM Consultants, ClientsWHERE Consultant.ConsultantID = Clients.ConsultantIDGROUP BY Consultants.Lastname
![Page 14: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/14.jpg)
![Page 15: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/15.jpg)
SELECT Consultants.Lastname, Consultants.Status, Clients.Lastname, Clients.CountType, Clients.Assets
FROM Consultants, ClientsWHERE Consultant.ConsultantID = Clients.ConsultantIDGROUP BY Consultants.Lastname
![Page 16: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/16.jpg)
The results are grouped according to the last name of the consultants.The records with the same last name are in the same group.
4
5
1
5
4
![Page 17: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/17.jpg)
Referential Integrity
Delete Record button
Click + to displayrelated records
You cannot delete a Consultant without first deleting related Clients
![Page 18: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/18.jpg)
fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno
Dname, dnumber, mgrssn, mgrstartdate
Dnumber, dlocation
Pname, pnumber, plocation, dnum
Essn, pno, hours
Essn, dependentname, sex, bdate, relationship
EMPLOYEE
DEPARTMENT
DEPT _LOCATIONS
WORKS_ON
PROJECT
DEPENDENT
![Page 19: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/19.jpg)
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)
![Page 20: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/20.jpg)
Cascade delete – a strategy to enforce referential integrity
ssn
Employee
Essn Pno
delete
Works-on
delete
![Page 21: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/21.jpg)
Cascade delete – a strategy to enforce referential integrity
Employee
delete
ssn supervisor
null
Employee
delete
ssn supervisor
null
delete
not reasonable
![Page 22: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/22.jpg)
Modify – a strategy to enforce referential integrity
ssn
Employee
Essn Pno
delete
Essn Pnonull
This violates the entity constraint.
Works-on Works-on
![Page 23: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/23.jpg)
Modify – a strategy to enforce referential integrity
ssn
Employee
delete
This does not violate the entity constraint.
Department
Dno
chairman
Department
null
Dno
chairman
![Page 24: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/24.jpg)
Chart Wizard
View button
Modified Y and Xaxis now matchthe query data
Chart from Wizard
![Page 25: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/25.jpg)
The User Interface
• Switchboard
• Switchboard Manager
• Switchboard Items table
![Page 26: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/26.jpg)
Other Access Utilities
• Convert Database command
• Compact and Repair Database command
![Page 27: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/27.jpg)
Compact the Database
View button
Select thedatabase
Click compact
![Page 28: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/28.jpg)
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
![Page 29: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/29.jpg)
Switchboard Manager
Each buttoncorrespondsto a command
![Page 30: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/30.jpg)
Chapter 4 Summary (1 of 2)
• One-to-many relationships• A query can display data from multiple
tables• Get External Data command• A total query performs calculations on• a group of records using summary
functions
![Page 31: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/31.jpg)
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
![Page 32: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/32.jpg)
Practice with Access
1. The Client Master List2. The HMO Database3. Creating a Switchboard4. The Look Ahead Databas5. Linking Versus Importing6. Pivot Tables7. Pivot Charts
![Page 33: Exploring Microsoft Access 2003](https://reader030.vdocuments.us/reader030/viewer/2022033023/568137e2550346895d9f87e1/html5/thumbnails/33.jpg)
Case Studies
• Your First Consultant’s Job
• The Wellness Center
• The Database Wizard
• Compacting Versus Compressing