1 / 30

Committed to Shaping the Next Generation of IT Experts.

Exploring Microsoft Access 2003. Chapter 4- Proficiency: Relational Databases, Pivot Charts, and the Switchboard Robert Grauer and Maryann Barber. Committed to Shaping the Next Generation of IT Experts. Objectives. Import data from Excel into Access database Create a one-to-many relationship

Sophia
Download Presentation

Committed to Shaping the Next Generation of IT Experts.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Exploring Microsoft Access 2003 Chapter 4- Proficiency: Relational Databases, Pivot Charts, and the Switchboard Robert Grauer and Maryann Barber Committed to Shaping the Next Generation of IT Experts. Exploring Office 2003 - Grauer and Barber

  2. Objectives • Import data from Excel into Access database • Create a one-to-many relationship • Create a report containing a relationships diagram • Use the Get External Data command • Create and modify a multiple-table select query Exploring Office 2003 - Grauer and Barber

  3. Objectives (continued) • Create a totals query • Use Microsoft Graph to create a chart based on a table or query • Create and modify a switchboard • Compact and repair a database Exploring Office 2003 - Grauer and Barber

  4. Case Study: Attorneys for Athletes Students create a database that includes: • Three Tables: offices, attorneys, and athletes • Two one-to-many relationships • A switchboard Exploring Office 2003 - Grauer and Barber

  5. Get External Data • Get External Data command • Export command • Import Spreadsheet Wizard • Import Text Wizard • Importing versus linking Exploring Office 2003 - Grauer and Barber

  6. Multiple-Table Queries • One-to-many relationship • Primary key • Foreign key • Referential integrity Exploring Office 2003 - Grauer and Barber

  7. Multiple Table Query Foreign key in Clients table Primary key in Consultants table Relationship between tables (one to many) Each field and table to display Exploring Office 2003 - Grauer and Barber

  8. Hands-on Exercise 1 • Title of Exercise: Importing Data From Excel • Objective: to import an Access table from an Excel workbook; create a one-to-many relationship, and create a multiple-table query • Input file: Investment workbook (Excel) Investment database (Access) • Output file: Investment workbook Investment database (modified) Exploring Office 2003 - Grauer and Barber

  9. Total Queries • Summary functions • Total row • Group By • Count function • Sum function Exploring Office 2003 - Grauer and Barber

  10. Total Query Click drop down arrow in Total row Select Count from drop-down menu Exploring Office 2003 - Grauer and Barber

  11. Pivot Tables and Pivot Charts • Pivot tables compute summary stats for the records according to supplied parameters • Pivot chart provides the same information in graphical form Exploring Office 2003 - Grauer and Barber

  12. Pivot Tables Exploring Office 2003 - Grauer and Barber

  13. Pivot Chart Exploring Office 2003 - Grauer and Barber

  14. Chart Wizard (1 of 6) Choose the Query Exploring Office 2003 - Grauer and Barber

  15. Chart Wizard (2 of 6) Select the fields to be included in the chart Inserts the selected field Removes only the selected field Inserts all fields Removes all fields Exploring Office 2003 - Grauer and Barber

  16. Chart Wizard (3 of 6) Select the chart type Description of The currently Selected chart type Exploring Office 2003 - Grauer and Barber

  17. Chart Wizard (4 of 6) Preview the chart Exploring Office 2003 - Grauer and Barber

  18. Chart Wizard (5 of 6) Title the chart Legend display option Exploring Office 2003 - Grauer and Barber

  19. Chart Wizard (6 of 6) The completed chart Exploring Office 2003 - Grauer and Barber

  20. Hands-on Exercise 2 • Title of Exercise: Total Queries, Charts, and Pivot Tables • Objective: to create a total query; to use Microsoft Graph to present data from an Access object in graphical form • Input file: Investment database (after exercise 1) • Output file: Investment database (modified) Exploring Office 2003 - Grauer and Barber

  21. The User Interface • Switchboard • Switchboard Manager • Switchboard Items table Exploring Office 2003 - Grauer and Barber

  22. Switchboard Manager Graphic About Investments form Exploring Office 2003 - Grauer and Barber

  23. Other Access Utilities • Convert Database command • Changes the file format to an earlier version of Access • Compact and Repair Database command • Compact: Reduces file size • Repair: Automatically run if unable to open an Access database Exploring Office 2003 - Grauer and Barber

  24. Compact the Database Select the Database to compact Click Compact Exploring Office 2003 - Grauer and Barber

  25. Hands-on Exercise 3 • Title of Exercise: The Switchboard Manager • Objective: to create a switchboard and user interface; to compact a database. • Input file: Investment database (after exercise 2) • Output file: Investment database (modified) Exploring Office 2003 - Grauer and Barber

  26. Chapter 4 Summary • One-to-many relationships • A query can display data from multiple tables • The Get External Data command • A Total query performs calculations on a group of records using summary functions Exploring Office 2003 - Grauer and Barber

  27. Chapter 4 Summary (continued) • Pivot table/chart displays summary stats for records in a table according to supplied parameters • Switchboard Manager creates the user interface • Convert Database command changes an Access 2000 file to a previous version Exploring Office 2003 - Grauer and Barber

  28. End-of-chapter Exercises • Multiple Choice • Practice With Access • Exercise 1 – The Oscars • Exercise 2 – Definitely Needlepoint • Exercise 3 – Metro Zoo • Exercise 4 – The Shopping Mall • Exercise 5 – Best Realty • Exercise 6 – The Fishing Pole • Exercise 7 – Database Wizard Exploring Office 2003 - Grauer and Barber

  29. End-of-chapter Exercises (continued) • Practice With Access (continued) • Exercise 8 – The HMO Database • On Your Own • Recreational Sports League • The Franchise • The Loan Officer Exploring Office 2003 - Grauer and Barber

  30. Questions? Exploring Office 2003 - Grauer and Barber

More Related