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

Introduction

Do more with Access queries
Trust the exercise files

Introduction to Access Queries

The Red30 Tech database
Understand queries
Create a query with the Query Wizard
Explore the Query Design view
Build a query in Design view

Create Simple Select Queries

Define query criteria
Understand comparison operators
Use wildcards in criteria
Rename the column headers
Explore the property sheet
Work with joins
Challenge - Create a select query
Solution - Create a select query

Create Parameter Queries

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

Use the Built-In Functions

Explore the Expression Builder interface
Use mathematical operators
Apply functions to text
Challenge - Convert US dollars to Canadian dollars
Solution - Convert US dollars to Canadian dollars

Aggregate Recordings with a Totals Query

Summarize data with aggregate functions
Understand the totals field
Calculate a sum total
Using the WHERE clause

Work with Dates in Queries

Select a range of dates or times
Date and time functions
Format dates
Sort dates chronologically
Obtain today's date
Calculate elapsed time with DateDiff()
Calculate time intervals with DateAdd()
Challenge - Expand order details
Solution - Expand order details

Program Flow Functions

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

Alternative Query Types

Find duplicate records
Identify unmatched records
Create an unmatched records query
Make a crosstab query
Create a backup of the database
Update data with a query
Make table, delete, and append queries

Write Queries with Structured Query Language

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

Useful Query Tricks

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

Conclusion

Next steps