Access 2021: Queries

Learn how to craft better queries—the key to finding raw data and transforming it into something usable—in Access 2021.

Introduction

• Do more with Access queries
• Trust the exercise files

1. 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

2. 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

3. 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

4. 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

5. Aggregate Recordings with a Totals Query

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

6. 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

7. 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

8. 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

9. Write Queries with Structured Query Language

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

10. 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