Microsoft Office XP Illustrated Introductory, Enhanced

1. Microsoft Office XP Illustrated Introductory, Enhanced Using Tables and Queries

2. Objectives • Plan a database • Create a table • Modify a table • Format a datasheet • Understand sorting, filtering, and finding Using Tables and Queries Unit B

3. Objectives • Sort records and find data • Filter records • Create a query • Modify a query Using Tables and Queries Unit B

4. Planning a Database • The most important object in a database is the table object. • Tables store the raw data, the individual pieces of information stored in fields • When designing a table, the fields of information and the type of data are identified • Some databases have multiple tables linked together Using Tables and Queries Unit B

5. Planning a Database (cont.) • Important planning items. • Determine purpose of database • Determine what reports need to be produced • Collect raw data • Sketch the structure of each table • The data type determines what type of information you can enter in a field Using Tables and Queries Unit B

6. Planning a Database (cont.) Data types Using Tables and Queries Unit B

7. Planning a Database (cont.) Example of a database structure Using Tables and Queries Unit B

8. Planning a Database (cont.) • Choosing between the Text and Number data type. • Avoid selecting the Number data type for a telephone number or zip code field • Use the Text data type • Enter a phone number with letters, such as a 800 number • Able to sort zip codes alphabetically Using Tables and Queries Unit B

9. Creating a Table • Methods for creating a table. • Import a table from another data source, such as a spreadsheet • Use the Table Wizard to create a table from scratch • Provides interactive steps to create field names and data types Sample fields Sample tables Using Tables and Queries Unit B

10. Creating a Table (cont.) Key symbol, which identifies a primary key A primary key is a special field that has unique information Using Tables and Queries Unit B

11. Modifying a Table • Each database object has a Design View to modify its structure. • In the Design view of a table, you can add or delete fields, change field properties, and add field descriptions • Field properties are additional characteristics of a field such as its size or default value Using Tables and Queries Unit B

12. Modifying a Table (cont.) Field description Data type column Symbol identifies selected field Field Properties pane Using Tables and Queries Unit B

13. Modifying a Table (cont.) • Learning about field properties. • Properties are the characteristics that define the field • Two properties are required for each field: Field Name and Data Type • Other properties include: Field Size, Format, Caption, and Default Value • Adding more properties to a field restricts the type and amount of data which increases data entry accuracy Using Tables and Queries Unit B

14. Modifying a Table (cont.) • Learning about field properties (cont.) • The Caption property is used to override the field names with an easy-to-read caption on datasheets, forms, and reports • Access Wizards create tables that have field names without spaces Using Tables and Queries Unit B

15. Formatting a Datasheet • Dramatically change the appearance of a datasheet. • Change fonts, colors, and gridlines Formatted table Using Tables and Queries Unit B

16. Understanding Sorting, Filtering, and Finding • Datasheet records are automatically sorted according to the data in the primary key field. • Sorting is reorganizing records in either an ascending or descending order based on the contents of a field • Filtering is temporarily isolating a subset of records • Finding is locating a specific piece of data Using Tables and Queries Unit B

17. Understanding Sorting, Filtering, and Finding (cont.) Sort, Filter, and Find buttons Using Tables and Queries Unit B

18. Understanding Sorting, Filtering, and Finding (cont.) Records sorted in ascending order Using Tables and Queries Unit B

19. Understanding Sorting, Filtering, and Finding (cont.) Sort Ascending button Sort Descending button Apply Filter or Remove Filter button Filtered by Rock category Using Tables and Queries Unit B

20. Understanding Sorting, Filtering, and Finding (cont.) • Using wildcards. • Wildcards are symbols you use a substitutes for characters to locate data that matches your Find criteria • Wildcards include: • The asterisk (*) represents any group of characters • The question mark (?) represents any single character • The pound sign (#) represents a single digit Using Tables and Queries Unit B

21. Sorting Records and Finding Data Records are sorted in ascending order by title Using Tables and Queries Unit B

22. Sorting Records and Finding Data (cont.) Search criteria Search direction Find the search criteria anywhere in the field Using Tables and Queries Unit B

23. Sorting Records and Finding Data (cont.) • Using more than one sort field. • Primary sort field (last name) • Secondary sort field is used to further sort records when there is a tie in the primary sort field • For example, two of the same last name (Smith) • Queries allow you to sort by more than one field Using Tables and Queries Unit B

24. Filtering Records • Filtering the datasheet temporarily displays only those records that match criteria. • Criteria are rules limiting conditions you set • To filter for comparative data and to specify more complex criteria you must use the Filter By Form feature • A comparison operator: equal to or greater than Using Tables and Queries Unit B

25. Filtering Records (cont.) Comparison operators Using Tables and Queries Unit B

26. Filtering Records (cont.) Both records match the criteria Filter By Form button Filter By Selection button Using Tables and Queries Unit B

27. Filtering Records (cont.) • Searching for blank fields. • The Is Null criteria will find all records where no entry has been made in the field • The Is Not Null criteria will find all records where there is any entry in the field, even if the entry is 0 • Primary key fields cannot have a null entry Using Tables and Queries Unit B

28. Creating a Query • A query is a database object that creates a datasheet of specified fields and records from one or more tables. • A query is like a filter but can be saved as a separate object unlike a filter • A query answers a question about the data in your database • A query is sometimes called a logical view of the data Using Tables and Queries Unit B

29. Creating a Query (cont.) • Editing data in a query. • Changes the data in the underlying table just as if you were working directly in the table’s datasheet. • A query does not produce a duplicate set of data • A query displays the original table’s data in a new arrangement Using Tables and Queries Unit B

30. Creating a Query (cont.) Design View button Query datasheet Using Tables and Queries Unit B

31. Creating a Query (cont.) Datasheet View button Field list for query Criteria cell for Category field Query design grid Using Tables and Queries Unit B

32. Creating a Query (cont.) Queries vs. filters Using Tables and Queries Unit B

33. Modifying a Query • Use Query Design View to modify a query. • Define complex criteria, define multiple sort orders, and build calculation fields • To add fields to the query, drag the field from the filed list to the open position in the query design grid • Sort orders are evaluated from left to right Using Tables and Queries Unit B

34. Modifying a Query (cont.) Drag fields from the Field list… …and drop them in the query design grid Using Tables and Queries Unit B

35. Modifying a Query (cont.) • Understanding And and Or criteria. • Criteria placed on different rows of the query design grid are Or criteria • A record may be true for either row of criteria • Additional criteria in the same row in the query design grid is And criteria • Records must meet the criteria for all of the criteria on one row Using Tables and Queries Unit B