Most of the time the fastest and easiest way to create a query is in Design view. Here's how:
Open the Lesson 2 database.
Here's how to create a simple query:
In the Database window, click the Queries icon in the Objects bar and double-click Create query in Design view.
The Show Table dialog box appears, as shown in Figure 2-10. Here you have to select the table or query you want to use.
Click Employees and click Add.
When you have finished adding the tables and/or queries to your new query, you can close the Show Table dialog box.
The Query window appears in Design view, as shown in Figure 2-11. Notice that the window is split. The top half contains a box labeled Employees, which displays all the fields in the Employees table. The bottom half of the screen contains a design grid, which is where you will add the fields you want to appear in your query.
You can add fields to the design grid in two ways:
Double-click LastName and FirstName in the Employees field list.
Access adds the LastName and FirstName fields to the design grid.
Often you will have to use the field list's scroll bar to scroll up or down the list in order to find a field.
Scroll down in the field list and double-click the City field.
Now you need to specify any criteria for the query. You enter the criteria in the design grid's Criteria row. For this exercise you want to see only the records whose City fields contain "London"move on to the next step to add this criteria to the query.
Click in the City column's Criteria row and type London.
If you want to use a field in the query, but you don't want it to be displayed in the query results, uncheck the "Show" box for that field. Your design grid should look like Figure 2-12.
Click the Show box for the City field to uncheck it.
The query will still use the criteria you specified for the City field, but it won't display the City field in the query results. You've created a simple query. Here's how to save it:
Click the Save button on the toolbar, type London Query and click OK.
OKlet's run our new query!
Click the Run button on the toolbar.
Tip: Other ways to run a query are to open the Query from the Database window, or click the Datasheet View button on the toolbar. You can also select Query
Access displays the results of the query.
Notice that while the City field is part of the query, it is not displayed because you unchecked its "Show" box back in Step 8.
Table 2-5 shows a smattering of criteria operators and examples to get you started.
Table 2-5. Common Criteria Operators
Finds records equal to MN.
Finds records not equal to MN.
Finds records less than 10.
Finds records less than or equal to 10.
Finds records greater than 10.
>=10 AND 5
Finds records greater than or equal to 10 and not equal to 5.
BETWEEN 1/1/99 AND 12/31/99
Finds records between 1/1/99 AND 12/31/99.
Finds text beginning with the letter "S." You can use LIKE with wildcards such as *.