Access 2019: Queries

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

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.

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