Selecting Data Using Queries in Microsoft Access 2013
- 3/15/2013
- Selecting all columns from a table
- 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
- Eliminating duplicate values
- 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
- Adding two sets of query results together
- Resolving ambiguous outer joins
- Creating an additional query to resolve a problem with mixed joins
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
Click the Create tab.
Click Query Design.
Click the table name in the Show Table popup window.
Click Add.
Close the Show Table popup window.
Double-click the asterisk (*) to add all the columns to the query grid.
Click View, and select Datasheet View.
)
)
)