Use queries to locate information in an Access database based on criteria that you specify as part of the query. Criteria are conditions that identify the specific records you are looking for. Criteria enable you to ask a more specific question, and therefore you will get a more specific result. For example, if you want to find out which student club members live in a particular town, you could limit the results to a specific city and only records that match the specified city would display.
In this activity, you will specify the criteria in the query so that only records in the Pledges table that have Student Government in the Club Affiliation field display. Records that indicate another club affiliation will not display. You will save the query with a new name.
With your 2A Query1 open, click the View button to switch to the Design view. In the design grid, locate the Criteria row as indicated in Figure 2.17.
The Criteria row is where you will specify the criteria that will limit the results of the query to your exact specifications.
In the Criteria row, under the Club Affiliation field, type Student Government Association Compare your screen with Figure 2.18.
On the Query Design toolbar, click the Run button to see the results, and then compare your screen with Figure 2.19.
Four records display that meet the specified criteriarecords that have Student Government Association in the Club Affiliation field.
Alert!: Do Your Query Results Differ?
If you mistype the criteria, enter it under the wrong field, or make some other error, the results will not display any records. This indicates that there are no records in the table that match the criteria as you entered it. If this occurs, return to the Design view and re-examine the query design. Verify that the criteria is typed on the Criteria row, under the correct fieldClub Affiliation, and that it is spelled correctly. Then rerun the query.
From the File menu, click Save As.
In the text box under Save Query 2A Query1 Firstname L. . . To: and using your own name, type 2A Student Government Pledges Firstname Lastname Click OK or press .
At the far right edge of the menu bar, click the Close Window button to close the query and display the name of your new query in the Database window.
So far, all of the fields that you included in the query design have also been included in the query result. It is not required to have every field in the query display in the result. In fact, there will be times when you will want to prevent some fields from displaying in the result.
For example, if you were querying your CD collection database to find out what records in the CD table were performed by a particular artist, you would need the CD Artist field in the query design, but you would not need the field to display in the query resultbecause the artist would be the same for all the records. Including the field would be redundant and not particularly useful.
Be sure Queries is selected on the Objects bar, and then double-click Create query in Design view.
In the displayed Show Table dialog box, double-click Pledges and then click Close. Expand the table area by dragging the border between the table area and the design grid downward. Then, expand the lower edge of the Pledges field list until all of the fields are displayed.
Using any of the techniques you have practiceddouble-clicking, dragging, or displaying the list arrowadd the following fields, in the order listed, to the design grid: Student#, Club Affiliation, Pledge Amount, and Date Collected.
In the Criteria row, under Club Affiliation, type Culinary Arts Club and then press . Compare your screen with Figure 2.20.
On the Show row, notice that the check box is selected for every field. Then, on the Query Design toolbar, click the Run button to see the seven records that display as a result of this query. Notice that all of the records display Culinary Arts Club in the Club Affiliation column.
On the Query Datasheet toolbar, click the View button to return to Design view. Under Club Affiliation, in the Show row, click to clear the check mark, and then compare your screen with Figure 2.21.
On the Query Design toolbar, click the Run button and compare your screen with Figure 2.22.
The query results display the same seven records but the Club Affiliation field does not display. Although the Club Affiliation field was still included in the query criteria for the purpose of identifying specific records, it is not necessary to display the field in the result.
Clear the Show check box when necessary to avoid cluttering the query results with redundant data.
Leave this query open for the next activity.
Sometimes you must locate records where specific data is missing. For example, you might want to know who has not paid their club dues for the year so you can send them a reminder notice. You can locate these records by using is nullemptyas a criteria on a Date Paid field. Additionally, you can display only the records where a value has been entered in a field by using is not null as a criteria, which will exclude records where the specified field is empty.
On the Query Datasheet toolbar, click the View button to return to Design view. From the Edit menu, click Clear Grid.
NoteClearing the Design Grid
The Clear Grid button is on the Filter/Sort toolbar because filters are intended to be temporary and easily created, cleared, or replaced. Queries on the other hand are intended to be saved so that they can be applied to the data repeatedly. It would not be typical to open an existing query, clear the grid, and start over; therefore, the Clear Grid button is not included on the Query Design toolbar. If a new query is needed, it is more common to start with the blank query design window.
From the Pledges field list, use one of the techniques you have practiced to add, in the order given, the following fields to the design grid: Student#, Donor First Name, Donor Last Name, Donor Phone, Pledge Amount, and Date Collected.
On the Criteria row, under the Date Collected field, type Is Null and compare your screen with Figure 2.23. Alternatively, type is null and Access will change the criteria to display with capitals.
On the Sort row, click under the Student# field, click the Sort arrow, and then click Ascending. Run the query to see the results, and compare your screen with Figure 2.24.
Eight pledges have not been collectedthe Date Collected field is empty for these pledges.
On the Query Datasheet toolbar, click the Save button , and then name this query 2A Uncollected Pledges Firstname Lastname and then click OK or press .
GO! with Microsoft Office 2003 Brief (2nd Edition)