Andrew Couch shows you how to work with Queries in Microsoft Access 2013.

In this section:

Selecting individual columns from one or more tables

Joining tables to see unmatched or missing records

Filtering by single and multiple combinations of choices

Adding calculations with the expression builder

Returning the top matched records

Creating a summary calculation

Prompting to filter data with parameters

Creating a crosstab query with the Query Wizard

Simplifying a problem with a query by using other queries

Queries are where a database can offer a real benefit over a spreadsheet because they allow you to construct sophisticated, updatable presentations of your data. These can vary in complexity from a simple filtered list based on a single table to more complicated presentations of summarized information involving many tables of data. Remember that a database encourages you to split your data into separate tables to reduce duplication of information when you are entering data, at the expense of having that data in several places. You will see that queries lie at the very heart of a database and allow you to reassemble the data from the different tables into a single view with greater flexibility than if the data were in one list.

The power of queries increases as you gain more experience with Access. For example, layering one query on top of another allows you to break down a complex problem into several simple steps. A query can display data both from tables and from other queries. Queries can also be parameterized to prompt users to enter filtering criteria, and they can be extended so that they are driven by selections that you make in forms. (See “Linking a form to a query” on page 224.)

Selecting all columns from a table

A query allows you to choose information from one or more sources, which could be either tables or other queries, and link your choices together to present your final results. The query design tool offers several methods for selecting your fields.

One key choice in how you construct a query is whether it will automatically include any new changes, such as adding new fields to the underlying table. Using TableName.* ensures that a query will include all fields from the underlying table, at the expense of being less efficient because it might include fields that you do not intend to use. However, if additional fields are later added to the underlying table, they will be shown automatically in the query results.

Select all columns from a table

To return to design view while viewing the data in the datasheet, click the Design View option in the View button drop-down list. The View button allows you to switch between working on your design and displaying the results.

Double-click the asterisk (*) to add all the columns to the query grid.