PowerPoint Presentation to Accompany GO! with Microsoft ® Office 2007 Introductory Chapter 13

1. PowerPoint Presentation to Accompany GO! with Microsoft® Office 2007 Introductory Chapter 13 Sort and Query a Database

2. Objectives • Open an Existing Database • Create Table Relationships • Sort Records in a Table • Create a Query in Design View • Create a New Query From an Existing Query

3. Objectives • Sort Query Results • Specify Criteria in a Query • Create a New Table by Importing an Excel Spreadsheet • Specify Numeric Criteria in a Query • Use Compound Criteria

4. Objectives • Create a Query Based on More Than One Table • Use Wildcards in a Query • Use CalculatedFields in a Query • Group Data and Calculate Statistics in a Query

5. Create Table Relationships • Access databases are relational databases because the tables in the databases can relate to each other. • Common fieldsare fields that include the same data in more than one table. • Place common fields in tables that are related, then define the relationship.

6. Create Table Relationships • Both tables include common fields • In first table, primary key • In second table, foreign key • Referential integrity ensures the validity of the data between related tables. • Join line between the two tables show the one-to-many relationship.

7. Create Table Relationships One-to-Many indicated

8. Sort Records in a Table • Sorting is the process of arranging data in a specific order. • Ascending–A to Z or lowest number to highest • Descending–Z to A or highest number to lowest • To sort records, click the arrow to the right of the field name.

9. Sort Records in a Table

10. Sort Records in a Table • To sort on two or more fields • Identify the outermost sort field (first level of sorting) • Identify the innermost sort field (second level of sorting) • Sort the outermost field first; then sort the innermost field.

11. Create and Use a Query • A query retrieves specific data from one or more tables. • Query means to ask a question. • Data sources are the table or tables from which queries get their data. • Access provides a wizard to walk step by step through the query process. • The wizard involves choosing the data source and fields.

12. Create and Use a Query • On Ribbon, click Create tab. • In Other group, click Query Wizard. • In New Query dialog box, click Simple Query Wizard, then OK. • In dialog box, select table. Select fields. • Follow wizard. Name query with a meaningful name.

13. Create and Use a Query

14. Create a Query in Design View • Queries created in Design view can be more complex. • Click Create tab on Ribbon, in Other group, click the Query Design button. • In the Show Table dialog box, click the first table, click the Add button, then close the dialog box. • Query window: field list (upper pane) and design grid (lower pane).

15. Create a Query in Design View

16. Create a Query in Design View

17. Create a New Query From an Existing Query • It is a good idea to save your queries. It is very likely you will need to ask the same question again. • Open a previous query. • From the Office menu, click Save As. • Save with a new name.

18. Create a New Query From an Existing Query

19. Sort Query Results • Results of a query can be sorted. • The process is similar to sorting a table. • Open a saved query. • From Office menu, click Save As. • Save with new name.

20. Sort Query Results

21. Sort Query Results • In Design view sorting can be done on two non-adjacent fields. • One field can be sorted in ascending order and the other in descending order. • Fields that have Sort designations are sorted from left to right.

22. Specify Criteria in a Query • Queries can locate information based on criteria specified as part of the query. • Criteria are conditions that identify the specific records you are looking for and enable you to ask more specific questions. • You do not have to have every field in the query display in the results. • In the Show row, click the box to clear the criteria you do not want to be shown.

23. Specify Criteria in a Query

24. Specify Criteria in a Query • To locate records where specific data is missing, use is null as a criteria. • To locate records where a value has been entered, use is not null as criteria.

25. Create a New Table by Importing an Excel Spreadsheet • Many users track their data in an Excel spreadsheet. • Sorting and filtering capabilities are useful for simple databases. • Excel is limited to one table and cannot relate data from multiple spreadsheets. • Data from Excel spreadsheets can become Access tables by importing the spreadsheet.

26. Create a New Table by Importing an Excel Spreadsheet

27. Create a New Table by Importing an Excel Spreadsheet

28. Specify Numeric Criteria in a Query • Criteria can be set for fields that contain numerical data. • Set the appropriate data type for fields that will contain numbers, currency, or dates so that mathematical calculations can be performed.

29. Specify Numeric Criteria in a Query • Comparison operators can be used to evaluate each field value. • Same = • Greater than > • Less than < • In between a range (Between...And) For example - Between 08/01/09 And 09/30/09

30. Specify Numeric Criteria in a Query

31. Use Compound Criteria • Compound criteria is used for specifying more than one condition in a query. • Logical operators ANDandOR are compound criteria. • AND displays records that meet both parts of the specified criteria. • OR displays records that meet either part of the criteria.

32. Use Compound Criteria

33. Create a Query Based on More Than One Table • In relational databases, information can be retrieved from more than one table. • Tables are joined by relating primary key field in one table to foreign key field in the other table. • Add the required fields. • Set the criteria on the Criteria row for the first criteria. • Set the second criteria on the OR row.

34. Create a Query Based on More Than One Table

35. Use Wildcards in a Query • Wildcard characters serve as a placeholder for one or more unknown characters in the criteria. • The asterisk (*) represents any group of characters. • The question mark (?) is used to search for unknown single characters. • Access adds the word like at the beginning of the criteria.

36. Use Wildcards in a Query

37. Use Calculated Fields in a Query • Queries can create calculated values. • First, name the field that will store the calculated values. • Second, write the expression that will perform the calculation. Each field name used in calculation must be enclosed within its own pair of square brackets, [ ]. • Zoom dialog box display gives you working space so you can see your calculation as you type it.

38. Use Calculated Fields in a Query

39. Use Calculated Fields in a Query

40. Group Data and Calculate Statistics in a Query • You can perform statistical calculations known as aggregate functions on a group of records. • Examples: AVG, SUM, MAX, and MIN • The aggregate functions can also be used to calculate totals by groups of data.

41. Group Data and Calculate Statistics in a Query

42. Covered Objectives • Open an Existing Database • Create Table Relationships • Sort Records in a Table • Create a Query in Design View • Create a New Query From an Existing Query

43. Covered Objectives • Sort Query Results • Specify Criteria in a Query • Create a New Table by Importing an Excel Spreadsheet • Specify Numeric Criteria in a Query • Use Compound Criteria

44. Covered Objectives • Create a Query Based on More Than One Table • Use Wildcards in a Query • Use Calculated Fields in a Query • Group Data and Calculate Statistics in a Query