Access 2021: Queries

Course details

Learn how to find and translate complex raw data into information you can use to make better decisions. Access expert Adam Wilbert explains how to create real-world queries to filter and sort data and perform calculations, as well as refine query results with built-in functions, all while offering challenges that help you master the material. Find out how to identify top performers, automate repetitive analysis tasks, make queries more flexible with parameter requests, and increase accuracy and consistency in your database using program flow functions. Adam closes with an assortment of useful query tricks. Take the challenges posed along the way to test and practice your new Access skills.

Concepts

0. Introduction

01 - Do more with Access queries
02 - Trust the exercise files

1. Introduction to Access Queries

03 - The Red30 Tech database
04 - Understand queries
05 - Create a query with the Query Wizard
06 - Explore the Query Design view
07 - Build a query in Design view

2. Create Simple Select Queries

08 - Define query criteria
09 - Understand comparison operators
10 - Use wildcards in criteria
11 - Rename the column headers
12 - Explore the property sheet
13 - Work with joins
14 - Challenge - Create a select query
15 - Solution - Create a select query

3. Create Parameter Queries

16 - Understand parameter queries
17 - Obtain parameters from forms
18 - Use a combo box to select criteria
19 - Requery with a button macro
20 - Challenge - Gather products by category
21 - Solution - Gather products by category

4. Use the Built-In Functions

22 - Explore the Expression Builder interface
23 - Use mathematical operators
24 - Apply functions to text
25 - Challenge - Convert US dollars to Canadian dollars
26 - Solution - Convert US dollars to Canadian dollars

5. Aggregate Recordings with a Totals Query

27 - Summarize data with aggregate functions
28 - Understand the totals field
29 - Calculate a sum total
30 - Using the WHERE clause

6. Work with Dates in Queries

31 - Select a range of dates or times
32 - Date and time functions
33 - Format dates
34 - Sort dates chronologically
35 - Obtain today's date
36 - Calculate elapsed time with DateDiff()
37 - Calculate time intervals with DateAdd()
38 - Challenge - Expand order details
39 - Solution - Expand order details

7. Program Flow Functions

40 - IIF() conditional statement
41 - Create an IIF() function
42 - Use the Switch() function
43 - Challenge - Calculate sales price for a product line
44 - Solution - Calculate sales price for a product line

8. Alternative Query Types

45 - Find duplicate records
46 - Identify unmatched records
47 - Create an unmatched records query
48 - Make a crosstab query
49 - Create a backup of the database
50 - Update data with a query
51 - Make table, delete, and append queries

9. Write Queries with Structured Query Language

52 - Explore the basics of SQL
53 - Create a union query to join tables
54 - Nest SQL code in other queries

10. Useful Query Tricks

55 - Pull random records from the database
56 - Return records above or below average
57 - Process a column of values with domain functions
58 - Challenge - Identify the highest and lowest pricing markup
59 - Solution - Identify the highest and lowest pricing markup

Conclusion

60 - Next steps