Access Data Analysis Cookbook

Chapter 1. Query Construction

Select queries are an essential part of any database system. These queries, which passively gather data (without changing the source data), are what we rely on to answer our questions about our data. In its most basic form, a select query merely returns records from a table verbatim. That’s not of much interest, since viewing the table itself would provide the same information. It’s when you add criteria, joins, and useful SQL functions and methods that select queries become valuable.

This chapter provides several tips for getting select queries to go the extra mile. Recipes in this chapter explain how to prompt for criteria at runtime, how to use logic operators to get the criteria just the way you need them, and how to handle duplicate records.

To make queries easier to read and work with, you’ll also find a recipe on using aliases, which provides a neat method to give nicknames to your tables. Another recipe explains how to use union queries to work around the problem of how to combine data from different tables so it can be treated as one source.

Finding Unmatched Records

Problem

I have a table that lists expenses incurred by employees. Some of these records do not match any records in the Employees table. How can I easily get a list of these unmatched expense records without having to examine every record in the table?

Solution

A special type of join called a left join (see Creating a Left Join) is used to identify records in one table that do not have ...