cmiller1137 and Ron McFadyen
Creating Queries
At this time, you have been able to create tables while entering data records into the database. With data now entered into the database, you can create a query that will display data from the tables in a database. A query is a saved Access object that is similar to asking the database questions which can display, sort, or filter this data into useful information.
Queries are used for multiple purposes in a database environment. Queries can be used to view table data that contains criteria to restrict the information a user can see. A variety of different query types can be created to view, change, add and delete data. As a result, queries can be executed to form the basis of a Microsoft Access form and report.
We shall use the Library database for the following Access query examples. With Microsoft Access, you can create a query in multiple ways with the select query being the most basic type of Access query. We will start creating our first query examples using Query Design. To create an Access query, click the Create tab and then click the Query Design icon:
Figure 4.1 Create a query
As a result, Access opens a Query By Example (QBE) window that you use to specify components of a query:
Figure 4.2 Query Design – QBE window
This window comprises two areas: Relationships and Grid. The Relationships area (upper window pane) will display each table and fields that need to be accessed along with the identified relationships used between those tables.
The Grid area (lower window pane) is used to specify:
- fields and tables,
- sort fields,
- fields to be included in the results display,
- different types of criteria can be applied in the row to obtain specific query information,
- calculations,
- grouping of rows with similar values in the field list to display summary information.
While using the Library database, we will start with simple query examples and progressively work our way to more complex scenarios.
4.1 Simple Query
The simplest query is one that displays all of the rows and columns in a table. In our first example, we want to list all of the books in the library. The process of creating the select query is as follows:
-
- Click on the Create tab (if necessary) and then click on the Query Design icon. Now, you can select the tables for your query in the Library database.
- If the Show Table dialog box has not displayed, choose the Show Table option in the query setup group.
- A Show Table window displays. From the list of tables, you must select the Book table. This task can be completed by double-clicking on the Book name or single click on Book and click on Add button.
Figure 4.3 QBE List of table names
-
- Choose Close from the Show Table pop up window.
Microsoft Access displays the Book table and its fields in the Relationships area. The first in this list is an * which stands for all attributes. To display all records in the Book table, double-click the asterisk symbol (*). This results