Creating and Using Select Queries

More Access Topics
Creating and Using Select Queries

Creating and Using Select Queries

Select queries allow you to filter or select which records you want to view. You can add conditions or criteria to queries to see specific records. Select queries can be saved and used as the basis for reports or even mailing labels.

Watch the video-Creating and using basic queries\ Watch the video-Creating and using complex queries\

No Fault Travel Database to complete this chapter

Creating and Using Select Queries

In this lesson you will create select queries and learn how to add criteria to select specific records.

1. Open the No Fault Travel database. Click the Create tab. Select Query Design in the Queries group.

The Show Table dialog box should appear where you can choose the Table(s) you wish to use in this query.

NOTE: For this lesson to work with the downloaded file, you must enter the data in the trips form as shown:

2. Select the Clients table and then click Add.

3. Close the Show Table dialog box or Add Tables pane depending on your version of Access.

You will now see the query grid. The top section of the query window displays a list of fields for each table you selected to use for this query.

Adding Fields to the Query Grid

The next step in creating a query is to choose the fields you want to use in the query. You choose the fields which you will view in the result and or use to identify which records you want to display by adding conditions or criteria.

You can add fields to the grid in three ways: 1) double-clicking on the field in the table field list, 2) dragging the field from the table field list down to the query grid, or 3) selecting the field from the Field drop down list in the grid. In the following exercise, you will use these methods to select which fields to use in the query.

1. Double-click the LastName field in the Clients field list.

The LastName field should appear in the first column of the query grid.

Next, you will place the FirstName field to the left of the LastName field by dragging the field from the Clients list onto the query grid.

2. Click on the FirstName field in the Clients field list in the query window.

3. Carefully drag the FirstName field directly onto the LastName field in the query grid.

You should see that Access inserted the FirstName field in front of the LastName field. Double-clicking to add fields to the query grid places them in the next available column. Dragging the fields from the field list allows you to place them in a specific column.

4. Add the Address1, Address 2, City, State, and Zip fields to the query grid by dragging or double-clicking them.

You can also add a field to a query by clicking the drop down arrow in the Field (top) row in the grid. You will add the ActiveCustomer field to the query using this method.

5. If needed, use the horizontal scroll bar in the query window to scroll to the grid column after Zip.

6. Click in the top row labeled Field in the blank column after the Zip column.

7. Click the down arrow, so that the drop-down list containing the field names for the Clients table appears. Scroll down to and choose ActiveCustomer.

You can use the drop down list to add any field from the selected table(s) to your query.

Viewing Query Results

To view the results of a query you can click the View tool on the ribbon. Access displays the query results in Datasheet View. At this point, you have selected the fields you want to use, but you have not specified any conditions for those fields which will allow you to view only records meeting that criteria.

1. From the active Query Tools Design tab, click the View tool in the Results group.

Access displays the query in Datasheet View. You will see an empty record at the end of the query. This is not actually a record in the database but is a row where a new record could be entered. This is why it displays the default values you have set for the State and ActiveCustomer fields. If you look at the record navigation bar at the bottom of the window, it indicates there are only four records in this query result table.

Sorting the Query Result

As you view the query result, you will notice the records are sorted by the ClientID field. You did not include the ID field in the query so it does not show in the results. However, by default, Access sorts the records in both a table and query result by the primary key. As you did in the datasheet (table) view, you can easily change the sort order of the query.

1. Click the View tool on the Home tab to return to the Design View of the query.

2. In the LastName field, click the drop-down arrow in the Sort row.

3. Choose Ascending from the drop-down list of sort choices.

You should notice the Sort row in the LastName column now displays Ascending, meaning the query will be sorted based on ascending order (A to Z) of this field.

4. Click the View tool again to view the query result.

This time the records in the query are sorted by last name.

Selecting Records in a Query

The query result is displaying all records in the Client table. Queries are so powerful because they allow you to specify only select records based on field criteria you add. In the next steps, you will specify that you only want to view records for active customers.

There are several methods of specifying criteria in fields. The criteria you enter also depends on the field’s data type; as an example, only Yes or No would be valid criteria in a YES/NO field.

You can specify exact or relational values as criteria for number and currency fields. As an example, entering >200 as criteria for a Cost field would cause Access to display only the records with a value greater than 200 in the Cost field. Math symbols which can be used as criteria in number and currency fields include:

< less than

> greater than

<= less than or equal to

>= greater than or equal to

* Wild Card character used to specify part of a field

There are other techniques and operators that you can use when building criteria and you will use some of these as you continue with this lesson.

1. Return to the Design View of the query.

You can do this by clicking the View tool on the Home tab.

2. Move to the criteria row of the ActiveCustomer column.

You may need to use the horizontal scroll bar to scroll to this column.

3. Type YES in the criteria row.

You will usually see YES/NO fields, such as ActiveCustomer displayed with the checkbox option. A check in the checkbox indicates yes and the absence of a checkbox indicates no. In the criteria, you enter the exact value you want to use to filter the records.

4. Click the View tool on the Home tab.

You are now viewing the results of the query with criteria you set. You should notice that one record, Robert Jones is not displayed in this query because he is not an active customer. The record count on the navigation bar should show only three records.

Saving a Query

After creating a query, you will usually want to save it. Saving the query lets you use the same criteria to view records at a later time. Saving the query also lets you create reports based on the query.

Each time you open a saved query, you are viewing current data. In other words, if more active clients were added to the Clients table, they would also appear in the query result. Each time you open the query, it reflects the current data in the database. Saving a query does not cause it to display only the records at the time the query was created and saved. The query result will change as the records in the database change.

1. Click on the Save tool on the Quick Access toolbar.

2. In the Save As dialog box, type Clients Currently Active in the Query Name text box and click OK.

3. Close the query but leave the database open.

You should now notice Access has listed another object, the query you just created in the Objects pane.

Using Additional Criteria in Queries

The real power in queries lies in their ability to select only certain records depending on the criteria that you enter. In this portion of the lesson you will explore different methods of entering criteria.

1. Select the Create tab, then click Query Design on the ribbon.

2. Add the Clients table and then close the Show Table dialog box.

3. Add the following fields to the query grid using any of the three methods you previously learned.

FirstName LastName City DOB

You will now add criteria to select records in which the City field contains first a specific city and then later either of two specified cities.

4. Move to the criteria row in the City field.

5. Type Boise in this row and press (Enter)

Notice that Access added quotation marks (“”) around Boise because criteria for text fields requires quotation marks. Access added them for you because the City field is a text field.

6. Click the View tool to view the query in Data sheet View.

The only records you should see in this list are those with Boise in the city field.

7. Switch back to Design View.

You can return directly to Design View by clicking on the Design View tool in the Views group on the Home tab on the ribbon. When you are in Datasheet View, the Design View tool is on the ribbon; when you are in Design View, the Datasheet View is on the ribbon.

8. Move back to the criteria row of the City field and erase the current criteria.

You can easily erase existing criteria by moving into the cell and pressing (Esc). This will select all cell contents. You can then press (Delete) to erase the existing criteria.

9. In the Criteria row of the City field type Caldwell or Nyssa and press (Enter).

Access will automatically add quotation marks around both cities. Access recognizes the Or in the criteria as an operator and does not add quotation marks.

10. Switch to Datasheet View.

You should now see only those records that live in either Caldwell or Nyssa.

11. Return to the Design View and then erase the criteria in the City field.

12. After erasing the criteria, type not Boise in the Criteria row of the City Field and then run the query.

The NOT operator allows you to select all records except those listed in the NOT statement for the field criteria. In this case, all records except those with Boise in the City field.

13. When done, close the query without saving.

This is not a query you will reuse. You just created it to experiment with different text field criteria options.

Using the Date() Function

Assume you want to create a list of trips that are scheduled to depart in the next 30 days. Displaying this list from a query will require you to add a criteria that selects a date range. An easy way to create a date range is to use the BETWEEN operator. The BETWEEN operator is inclusive, which means the two dates defining the between range will also be included in the range.

If you want to use a query at a later time, avoid placing the actual dates in the criteria. “Hard coding” dates makes a query obsolete when the date changes and would require you to change the criteria every time you use it. The Date() function solves this problem because it returns the current date and can be used in the criteria of a query.

In this portion of the lesson you will use the BETWEEN operator and the Date() function to create a query that selects records based on a date range.

1. Click the Create tab, and then the Query Design tool to create a new query.

2. Select the Trips table in the Show Table dialog box.

3. Close the Show table dialog box and add the fields: ClientID, Destination, Cost, DepartureDate and ReturnDate

4. Move to the Criteria row of the DepartureDate field and type between date() and date()+30 and press (Enter).

With this criteria, only records with trips departing in the next 30 days are selected. You may recall using the current date for the departure date when you entered records. This is to ensure you have some trips in the result of this query.

5. Click the DataSheet View tool to display the results of this query.

You should see only the trips specified in your criteria. If you do not see the records you expected, open the Trips table and edit some departure dates as today’s date.

The dates in your records will be different from those shown above.

6. Save this query as Trips departing in the next 30 days and then Close the query.

You should notice this query in the objects pane after saving it.

Creating Queries Based on Related Tables

In order to see fields from two different tables, such as a client’s name and the details of the client’s trip, you must create a query based on more than one table. However, when you create queries based on related tables the only records that appear in the query result are the records that are related to each other.

For example, if you create a query in the No Fault Travel database based on the Clients and Trips tables, the only clients in the query result will be clients with information in the Trips table. Similarly, if you create a query based on Travel Agents and Clients, the only clients in a query result are those with an assigned travel agent. Likewise, the only travel agents included are those with clients assigned to them.

In the last part of this lesson, you will create a query based on related tables. Even though you will not add any criteria, Access will automatically exclude records that are not related.

1. Click the Create tab, and then select Query Design tool on the ribbon.

2. Choose the Clients table and then close the Show Table dialog box.

3. Add the FirstName and LastName fields to the query.

Remember, to add fields you can double-click, drag, or use the drop-down list in the Field row of the query grid.

4. Click the View tool to see the result of this query and verify that all clients display in the query result.

Now, you will add the Trips table to this query. By doing so you will eliminate all the clients who have no records in the Trips table.

5. Click the View tool to return to Design View.

6. On the Query Tools Design tab, click the Show Table tool in the Query Setup group.

You can use the Show Table dialog box to add tables to a query.

7. Select the Trips table and then click Add.

8. After adding the Trips table, click Close to close the Show Table dialog box.

You should now see both tables in the query grid. The join line informs you that the two tables are related by a relationship you created in a previous lesson.

9. Without adding any additional fields, click the View tool to see the result of the query.

In viewing this query result, notice that Robert Johnson and Becky Adams appear in the list more than once. Mr. Johnson and Ms. Adams have more than one record in the Trips table. You should also notice that Robert Jones does not appear at all because he has no related records in the Trips table.

As you can see, adding related tables to a query affects the result. Normally, adding related tables to a query reduces the number of records in a query result.

10. Return to the Design View of the query. Add the DepartureDate, ReturnDate, Destination, and Cost fields from the Trips table.

11. Display the result of this query.

Additional information for each trip is now displayed. The dates in your data, as we have already mentioned will be different from the image above.

12. Save this query as Clients and Trips and then close the query.

You created this last query to help you understand how adding a related table affects the records that will show in the result of the query. Now that you have this concept down, you will improve a query you created earlier.

13. Display the Trips departing in the next 30 days query in design view.

You can right+click the query name and choose Design View as a shortcut.

14. In design view, click the Show Table tool and add the Clients table to this query.

15. Add the FirstName and LastName fields to this query, just after the ClientID column.

16. View the result of the modified query.

You should now see the client names along with the dates and cost of the trips departing in the next 30 days. As already discussed, your dates should be different from those shown.