Creating and Running Queries

We have said that databases often contain vast amounts of information - queries allow us to locate, sort and display just the information that interests us. The result of running a query looks like a table but with fields (columns) and some records (rows) in accordance with the way we choose to filter the information. Queries are a fundamental means of accessing and displaying data from tables. Queries can access a single table or multiple tables. Examples of queries for our bank database might include:

In this section, we show how to use the Access Wizards to create queries for a single table and for multiple tables.

4.1 Single Table Queries

Creating a query can be accomplished by using either the query design view or the Query wizard. In the following example, we will use the query wizard to create a query.

Queries are accessed by clicking on the Queries tab in the Access main screen. This is shown below:

To create a new query, double-click on 'Create Query by Using Wizard'. This is the quickest way to launch a simple query but be aware that other types of query are available by clicking the New button.

The first step in the Simple Query wizard is to specify the table for the query and which fields (columns) should be displayed in the query output. Three main sections of this step are:

1. Tables/Queries - A pick list of tables or queries you have created.
2. Available Fields - Those fields from the table that can be displayed.
3. Selected Fields - Those fields from the table that will be displayed.

For this example, pull down the Tables/Queries list and choose the Customer table. Notice that the available fields change to list only those fields in the Customer table. This step is shown below:

From the list of Available fields on the left, move the Name, Address, City and State fields over to the Selected Fields area on the right. Highlight one of the fields and then click on the right arrow button in the centre between the two areas. Repeat this for each of the four fields to be displayed. When done with this step, the wizard should appear as below:

Click on the Next button to move to the next and final step in the Simple Query wizard.

In the final step, give your new query a name. For this example, name the query: CustomerAddress

At this point, the wizard will create the new query with the option to either:

For this example, choose Open the query to view information and click on the Finish button. When this query executes, only the customer's name, address, city and state fields appear, however, all of the records (rows) appear as shown in the figure below:

Close this query by clicking its close box or choosing File > Close. The Access main screen showing the Queries tab should appear. Note the new query CustomerAddress appears when the Queries tab is selected.

We will now modify the CustomerAddress query to only display customers in a certain state, Georgia. To accomplish this, we will make use of the Query Design View.

Open up the CustomerAddress query in the design view by highlighting the name of the query and clicking on the Design button. The design view will appear as in the figure below:

The Query Design view has two major sections. In the top section, the table(s) used for the query are displayed along with the available fields. In the bottom section, those fields that have been selected for use in the query are displayed.

Each field has several options associated with it:

For this example, we will filter the records to only display those customers living in the State of Georgia (GA). We will also sort the records on the City field.

To sort the records on the City field, click in the Sort area beneath the City field. Choose Ascending from the list as shown in the figure below:

To filter the output to only display Customers in Georgia, click in the Criteria area beneath the State field and type the following statement:    ='GA'

The      ='GA'      statement tells Access to only show those records where the value of the State field is equal to 'GA'.

Run the query by choosing Query > Run or simply click the Run icon . The output is shown in the figure below:

Finally, save and close this query to return to the Access main screen.

4.2 Exercise: Single Table Queries

For this exercise, use the Simple Query wizard to create a query on the Accounts table showing just the AccountNumber, AccountType and Balance fields.

1. From the Access main screen, click on the Queries tab. Then double-click 'Create query by using wizard'.
2. Under Table/Queries: choose the Accounts table. Then move the AccountNumber, AccountType and Balance fields over to the Selected fields area. Then click the Next button.
3. In the next panel, you will be asked to choose between a detail or summary query. Choose detailed query and click on the Next button.
4. Name the new query : AccountsQuery and click on the Finish button.

The output is shown below:

Close this query.

In the next part of the exercise, we will modify the query to sort the output on the account number and only display the Savings accounts.

1. From the Queries tab on the Access main screen, highlight the AccountsQuery and click on the Design button.
2. Change the Sort order for the AccountNumber field to Ascending.
   Add the following statement to the Criteria: are under the AccountType field:
   = 'Savings'

   

3. Run the query by pulling down the Query menu and choosing the Run menu item, or just click the Run icon . The output is shown below:

   

4. Finally, save and close the query to return to the Access main screen.

4.3 Multiple Table Queries

Before proceeding with these next instructions, make certain the One-to-Many relationship between the Customers and Accounts table has been created (see section 2.4 'Creating Relationships' for a review of this process).

To start the process of creating a multiple table query, highlight the Query tab and double-click 'Create query by using wizard'. When the simple query wizard appears, select the Name field from the Customers table, then switch the Tables/Queries selection to the Accounts table and select the AccountType and Balance fields from the Accounts table. The result from this step is down below:

Note that it is not necessary to include either the CustomerID fields in the query even though it is these fields that provide the link between the two tables. Click the Next button to continue. In the next step of the wizard, an option will appear to provide some level of Summary. For this example, leave the default at "Detail ..." and then click on the Next button. 

In the final step of the wizard, name the query "Customer Accounts Query" and click on the Finish button. The multiple table query results should appear as follows:

As with single table queries demonstrated previously, one can change the query definition in design view by adding filters (e.g., show account information for all customers in 'GA').

4.4 Exercise: Multiple Table Queries

The resulting query should appear as follows:

4.5 Review of Creating and Running Queries

Creating a query using the query wizard:

1. From the Access main screen, click on the Queries tab. Then click on the New button.
2. From the Queries tab on the main Access screen, click on the New button and choose the Simple Query wizard option.
3. Under Table/Queries: choose the appropriate table for the query and then indicate which fields in the table will appear in the query output. If querying more than one table, change the Table/Queries: selection to display additional tables and select the necessary fields.
4. If the table contains numeric fields, either detailed or summary information may be specified for the query.
5. Finally, name the new query and click on the Finish button.

As a final note, Forms and Reports can be created based on existing queries.

4.6 Structured Query Language (SQL)