Lesson 10 – Designing Advanced Queries

1. Lesson 10 – Designing Advanced Queries

2. Lesson Objectives • Create and use select queries. • Create and use summary queries. • Work with summary query options. • Create and use a parameter query. • Create and use action queries.

3. Creating Select Queries Exercises • Create a Query from a Filter • Create a Subquery

4. Create a Query from a Filter • Convert a datasheet filter to a query

5. Create a Subquery • Queries create a dynaset using recordsets • Subqueries create a dynaset using other queries • Can help reduce complexity • Can increase speed to create the dynaset

6. Creating and Using Summary Queries Exercises • Create a Summary Query Using a Wizard • Create a Summary Query in Design View

7. Create a Summary Query Using a Wizard • Summary queries show aggregate values for a recordset

8. Create a Summary Query in Design View • Summary queries require the Total row • By default, all fields are set to “Group By” • Aggregate Functions • Sum & Avg functions • Numeric data types • Count function • Any data type

9. Working with Summary Query Options Exercises • Use Aggregate Functions in a Query • Use the Month Function in a Query • Use the MonthName Function in a Query • Use the Year Function in a Query • Enter a Calculation in a Query • Create a Summary Query

10. Use Aggregate Functions in a Query • Summarized data can be used to forecast trends

11. Use the Month Function in a Query • The Month Function extracts the numeric value of each month from a data value

12. Use the MonthName Function in a Query • The MonthName Function extracts the name of each month from a data value

13. Use the Year Function in a Query • The Year Function extracts the numeric value of year from a data value

14. Enter a Calculation in a Query • Subtracting two dates will give you the duration between the two dates

15. Create a Summary Query • Summary queries can contain many data calculations

16. Creating and Using a Parameter Query Exercises • Run a Parameter Query • Create a Parameter Query • Set Data Types in a Parameter Query • Use Multiple Criteria in a Parameter Query • Use a Parameter Query with Wildcards • Use a Parameter Query with a Report

17. Run a Parameter Query • Parameter queries prompt the user to type a value • The typed value is then used as a criterion

18. Create a Parameter Query • Create a select query • Add a criterion and test • Replace the criterion with a prompt with square brackets

19. Set Data Types in a Parameter Query • A parameter query assumes the data type of the parameter is Variant • Variant data types are inefficient • If the criterion is expecting a date, the parameter should be set to Date/Time data type

20. Use Multiple Criteria in a Parameter Query • Queries can have more than one criterion • Queries can have more than one parameter

21. Use Multiple Criteria in a Parameter Query • Parameters work the same as criteria when creating “AND” and “OR” queries

22. Use a Parameter Query with Wildcards • To use wildcards in a criterion or parameter you must use the keyword “LIKE”

23. Use a Parameter Query with a Report • Reports are based on recordsets • If the recordset contains a parameter, one report can show data based on the parameter value

24. Creating and Using Action Queries Exercises • Create an Update Query • Edit an Update Query • Create a Make-Table Query • Create a Delete Query • Create an Append Query

25. Create an Update Query • Update queries can make global changes to a group of fields

26. Edit an Update Query • This type of query is normally used only one time • Can be saved and edited

27. Create a Make-Table Query • Used to copy data from one recordset to a new table • Can have criteria

28. Create a Delete Query • Used to delete records from a recordset based on a criterion

29. Create an Append Query • Used to join records from different recordsets

30. Lesson 10 Summary • Queries can be categorized as select queries, summary queries, parameter queries, and action queries. • A select query locates data from one or more tables and displays the dynaset in a defined order. • A filter can be used to create a select query. • When you use a query within a query, you create a subquery. • Summary queries are select queries that display summarized rather than individualized data. • For each summarized field in a query, Access creates a caption on the basis of the function and the name of the field. • Aggregate functions summarize large or complex data.

31. Lesson 10 Summary • The Month function extracts the numeric value of the month from a date/time value. • The MonthName function extracts the name of the month from a date/time value. • The Year function extracts the numeric value of the year from a date/time value. • The numeric values of two dates can be subtracted to display a numeric value. • A parameter query prompts the user for criteria each time it runs. When you create a parameter query, you must first choose the field or fields on which comparisons are to be made. • Wildcards can only be used with text and numeric fields.

32. Lesson 10 Summary • When you create a parameter, Access sets the format to the default variant data type. • If you place two parameters on the same criteria row, both criteria must match for the record to be included in the dynaset. If you place parameters on separate rows, either criterion must match for the record to be included in the dynaset. • A make-table query is an action query that creates a new table. A delete query is an action query that deletes a group of records. An append query is an action query that adds a group of records to the end of a table. An update query is an action query that makes global changes to a group of records.