Access 2019: Queries

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

Introduction

• Do more with Access queries
• Follow along with the exercise files

1. Introduction to Access Queries

• The H+ Sport database
• Understand queries
• Create a query with the wizard
• Create a summary query with the wizard
• Build a query in design view
• Modify the query design interface

2. Creating 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. Creating Parameter Queries

• Understand parameter queries
• Obtain parameters from forms
• Use a combo box to select criteria
• Challenge: Gather employee emails based on department
• Solution: Gather employee emails based on department

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 Records with a Totals Query

• Summarize data with aggregate functions
• Understand the Totals field
• Using the WHERE clause

6. Working with Dates in Queries

• Dates as serial numbers
• 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. Understanding 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
• Add information to a crosstab query
• Create a backup of the database
• Update data with a query
• Make table, delete, and append queries: Part 1
• Make table, delete, and append queries: Part 2

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