Access 2010

One-table queries

Let's familiarize ourselves with the query-building process by building the simplest query possible: a one-table query.

We will run a query on the Customers table of our bakery database. Let's imagine that our bakery is having a special event, and we want to invite our customers who live nearby since they are the most likely to come. This means we need to see a list of all of the customers who live close by, and only those customers.

If you think this sounds a little like applying a filter, you're right. A one-table query is actually just an advanced filter applied to a table.

To apply a simple one-table query:

1. Select the Create tab on the Ribbon, and locate the Queries group.
2. Select the Query Design command.
   The Query Design Command
3. Access will switch to Query Design view. In the Show Table dialog box that appears, select the table you would like to run a query on. Click Add, then click Close. We are running a query about our customers, so we will add the Customers table.
   Selecting a table to use in the query
4. The selected table will appear as a small window in the Object Relationship Pane. In the table window, double-click the field names you would like to include in your query. They will be added to the Design Grid in the bottom part of the screen.
   
   In our example, we want to mail invitations to customers who live in a certain area, so we'll include the first and last name, street address, city, state, and zip code fields. We aren't planning on calling or emailing our customers, so we don't have to include the telephone or email fields.
   Selecting fields to add to the query
5. Set the search criteria by clicking on the cell in the Criteria: row of each field you would like to filter. Typing criteria into more than one field in the Criteria: row will set your query to include only results that meet all the criteria. If you want to set multiple criteria but don't need the records shown in your results to meet them all, type the first criteria in the Criteria: row and additional criteria in the Or: row and the rows beneath it.
   
   For this one-table query, we'll use very simple search criteria.
   • We want to find our customers who live in a city called Raleigh, so in our City field, we'll type "Raleigh". Typing "Raleigh" in quotation marks will retrieve all records with an exact match for "Raleigh" in the City field.
   • Some customers who live in the suburbs live fairly close, and we'd like to invite them as well. We'll add their zip code, 27513, as another criteria. Since we want to find customers who either live in Raleigh or in the 27513 zip code, we'll type "27513" in the or: row of the Zip Code field.
   Setting the search criteria so that the query will find records with either "Raleigh" in the City field or "27513" in the Zip Code field.
6. After you have set your criteria, run the query by clicking the Run command on the Query Tools Design tab.
   The Run Query command
7. The query results will be displayed in the query's Datasheet View, which looks like a table. If desired, save your query by clicking the Save command in the Quick Access Toolbar. When prompted to name it, type in the desired name, then click OK.
   Naming the new query to save it