Lesson 31: Querying a Database

1. Lesson 31: Querying a Database

2. Learning Objectives • After studying this lesson, you will be able to: • Create, save, and run select queries • Design a query using multiple tables • Set query criteria • Define a query sort order • Create and format a calculated field • Use functions in query expressions • Create a crosstab query • Create unmatched and duplicates queries

3. Queries Defined • Queries are database objects that enable you to ask questions of a database in order to retrieve data and extract records that meet specific criteria

4. Reviewing Query Features • Query results datasheet data remains stored in its original table rather than in the query • Data edited in a query results datasheet changes data stored in a table • Queries are dynamic objects that display up-to-date data stored in database tables • Queries can be used to create forms and reports containing fields from multiple tables • Query results datasheets enable you to filter or organize data using the same techniques you use to filter and organize table datasheets

5. Creating Select Queries • Access contains two distinct tools for creating select queries: • Query Wizard • Design View

6. Using the Simple Query Wizard • Steps you through the creation of a select query • Enables you to identify table(s) and fields from each table that you want to include in the query • Prompts you to save the query

7. Using Query Design View • Presents a split window with table field lists at the top and columns and rows at the bottom

8. Adding Fields to the Query Grid • Double-click a field name to add the field to the next available column of the query design grid • Drag a field to the next column in the grid • Click the Field row of a column in the query grid and selecting the field from the drop-down list • Double-click the asterisk (*) that appears at the top of the field list to add all fields to the grid • Double-click the field list title bar to place each field in the table in a separate column of the query grid. Note! When you use the asterisk to add all fields, Access places the table name in field row, but when you run the query, each field appears in a separate column of the query results datasheet.

9. Setting Query Criteria Wildcard Dates Comparison Operator Value Wildcard

10. Setting AND and OR Criteria • AND Criteria • OR Criteria

11. Sorting a Query and Limiting Results • Sorting Query Results • Using sort tools after running the query • Setting a sort order in the query grid Sort row

12. Limiting Results • Effective way of limiting results combines sorting with restricting the number of results to display • The Return feature on Query Design tab enables you to set the number of records you want to retrieve

13. Please Excuse My Dear Aunt Sally • Parentheses • Exponentials • Multiplication/Division • Addition/Subtraction (2+2)*32=6/2=33

14. Calculated Fields Have no value of their own Perform calculations using other field values Field names from existing tables Arithmetic or comparison operator Calculated field name

15. Using Functions in Queries • Groups query results by field on which a sum or average or other order is required • Multiple fields and calculated fields can be used for grouping • Example shows minimum, maximum, and average

16. Crosstab Queries • Rearranges data for summarizing • Groups data and totals values

17. Groupings appear as row and column headings Summarized values appear in the TOTAL area Crosstab Query Palette

18. Unmatched and Duplicates Queries • Checks database tables to ensure there is no duplication of records • Also checks database to ensure that records referenced in one table have a matching record in the other table • Hoped-for result of running these two queries is that Access finds nothing to report

19. Structured Query Language • The underlying structure created for queries in Access

20. Lesson 31: Querying a Database