1 / 73

Microsoft Office Access 2007 Lab 3

Microsoft Office Access 2007 Lab 3. Querying Tables and Creating Reports. Objectives. Evaluate table design Establish relationships Enforce referential integrity Create and modify a simple query Query two tables. Objectives. Filter a query Find unmatched and duplicate records

tristessa
Download Presentation

Microsoft Office Access 2007 Lab 3

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. Microsoft Office Access 2007Lab 3 Querying Tables and Creating Reports

  2. Objectives • Evaluate table design • Establish relationships • Enforce referential integrity • Create and modify a simple query • Query two tables

  3. Objectives • Filter a query • Find unmatched and duplicate records • Create a Parameter query • Create reports from tables and queries • Display a Totals row

  4. Objectives • Modify a report design • Select, move, and size controls • Change paper margins • Preview and print a report • Compact and back up a database

  5. Relationship Query Join Report Concept Preview

  6. Outline • Filtering Records • Using Filter by Selection • Using Filter by Form

  7. Outline • Querying a Database • Using a Query Wizard • Moving Columns • Modifying and Saving a Query • Creating a Query in Design View • Querying Two Tables • Finding Unmatched Records • Adding a Second Table to the Query

  8. Outline • Creating a Report from a Table • Using the AutoReport Wizard • Using the Report Wizard • Modifying the Report Design • Selecting the Report Design • Moving Controls • Sizing Controls • Previewing the Report Layout

  9. Outline • Creating a Report from a Query • Previewing and Printing Reports • Changing Page Margins • Printing an Entire Report • Printing a Selected Page of the Report • Compacting and Backing Up the Database

  10. Outline • Key Terms • FAQs • Discussion Questions

  11. Refining the Database • Once created, analyze your database for efficiency • There may be a better way to organize your table information • Is information repeated? • Are there any fields that need to be added? • Use the Access Table Analyzer to evaluate the design of your database

  12. Evaluating Table Design

  13. Table Analyzer Wizard New Table

  14. Table Analyzer Wizard

  15. Creating a Table List Lookup Field

  16. Deleting a Table

  17. Defining and Modifying Relationships

  18. Concept 1 Relationship • Establishes the association between common fields in two tables • Related fields • Must be the same data type • Must contain the same kind of information • Can have different field names • Special exceptions for the AutoNumber data type

  19. Types of Relationships

  20. Viewing Relationships

  21. Deleting Relationships 2. Delete the Table 1. Remove Relationship

  22. Enforcing Referential Integrity

  23. One-to-One Relationship with Referential Integrity

  24. Creating a Relationship

  25. Creating a Filter

  26. Querying a Database • A query is a request for specific data • Queries are used to … • View data in different ways • Analyze data and change existing data • Queries are based on tables

  27. Concept 2 Query

  28. Using a Query WizardSteps • Click Query Wizard in the Other group of the Create tab • Select resource table and fields • Enter a descriptive name for your query • Select how to display query results

  29. Types of Queries Using the Wizard

  30. Using the Query Wizard

  31. Filtering a Query

  32. Using Query Design View • Create or modify a query • Screen divided into two areas • Upper area contains the field list • Lower area displays the design grid

  33. Adding a Second Table to the Query What do you call a query that uses results from two or more tables?

  34. Concept 3 Join • An association between a field in one table or query and a field in another table or query • Must have at least one common field • Fields must be the same data type • Used to bring information from different tables together • Joins are what make relational databases so powerful

  35. Querying Two Tables

  36. Adding Fields • These methods can be used to add fields to the query design grid • Select field name and drag • Double-click on the field names • Select the field drop-down arrow and choose field name • Select the asterisk in the field grid to include all fields

  37. Basic Types of Joins

  38. Running a Query

  39. Specifying Criteria • A criteria expression will select only records that meet certain limiting criteria • Criteria can be entered in the Criteria row of the query in Design View • Not necessary to enter = (equal to) • Character strings are automatically enclosed in quotation marks

  40. Hiding and Sorting Columns

  41. Rearranging the Query Datasheet

  42. Finding Unmatched Records • Click in the Query Wizard in the Other group of the Create tab • Choose Find Unmatched Query Wizard

  43. Finding Duplicate Records

  44. Creating a Parameter Query

  45. Displaying a Totals Row • Calculations that are performed on a range of data are called aggregate functions • Averages • Sums • Counts • Data type of field must be number, decimal, or currency • Count function can be used on all data types

  46. Count Aggregate

  47. Creating Reports

  48. Concept 4 Report • Professional-appearing output • Generated from tables or queries • Includes groups and summary information • Can create … • Formatted labels • Report titles • Headings • Design styles, layouts, and graphics

  49. Methods for Creating Reports

  50. Using the Report Tool

More Related