6 Creating and Running Queries

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:

• Which Customers live in Georgia ?
• Which Accounts have less than a $500 balance ?

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

6.1 Single Table Queries

• only displays certain fields (columns) in the output
• sorts the records in a particular order
• performs some statistics on the records such as calculating the sum of data values in a column or counting the number of records, or
• filters the records by showing only those records that match some criteria. For example, show only those bank customers living in GA.

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, click on the New button. The New Query menu will appear as below. Select the Simple Query wizard option and click the OK 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 center 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: Customer Address

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

• Open the query to view information - that is, the wizard will execute the query and show the data.
• Modify the query design - the wizard will switch to the Design View to allow further modification of the query.

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

Close this query by pulling down the File menu and choosing the Close menu item. The Access main screen showing the Queries tab should appear. Note the new query CustomerAddress appears under the Queries tab.

In the following example, we will modify the CustomerAddress query to only display customers in a certain state. 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:

• Field - The name of the field from the table
• Table - The table the field comes from
• Sort - The order in which to sort on this field (Ascending, Descending or Not Sorted)
• Show - Whether or not to display this field in the query output
• Criteria - Indicates how to filter the records in the query output.

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 pulling down the Query menu and choosing the Run menu item. The output is shown in the figure below:

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

6.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 click on the New button.
2. Choose the Simple Query wizard option and click on the OK button.
3. 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.
4. 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.
5. Name the new Query : AccountsQuery and click on the Finish button.

The output is shown below:

Close this query by pulling down the File menu and choosing Close.

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. The output is shown below:

   

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

6.3 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.
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.

On to the Next Section