Step Four - Specific Criteria for a Query

The main purpose of queries is to see the a specific subset of data from a table. If we did not enter in any criteria, would have something that is the same as the datasheet view for the tables.

Simple Criteria Query

We are going to create a new query.

  1. Click the Query tab in the main window (if not already there) and click the new button.
  2. Click Design view and click the OK button.
  3. Click the tblProduct table entry.
  4. Click the Add button.
  5. Click the Close button.
  6. In the first column, click the Field box and then click the down arrow. Select ProductName from the list.
  7. In the second column, click the Field box and then click the down arrow. Select CostPrice from the list.
  8. In the third column, click the Field box and then click the down arrow. Select Category from the list.

This gives us the fields we want to use for the query. Now we are going to enter some criteria so that we only get a specific subset of information returned in the query.

Let's say we only want the entries in the "Pop" category.

  1. In the Criteria box in the Category column, type in "Pop" (without the quotations and not case sensitive).
  2. Press the down arrow key on the keyboard.

See what happen to the entry. Access automatically puts quotations around the phrase because it knows that it is a text string. So, you have the option of leaving them on or you can type them in yourself.

Run the query by clicking the Run button or select Query, Run from the menu.

You should see the following results:

To return to the Design window, click the Design button or select View, Design View from the menu.

Dealing with Numbers

There are many different ways the we can deal with numbers. The most commons ones are when we are looking for values less than something or values that are greater than something.

Let's look for all products that have a CostPrice less than 90 cents.

  1. Once in the Design View, delete the "Pop" criteria for Category by highlighting the Criteria box and pressing the Delete key on the keyboard.
  2. In the the Criteria box in the CostPrice column, enter in "< .9".
  3. Run the query by clicking the Run button or select Query, Run from the menu.

On the status bar of the dynaset window, it will say how many records fit the criteria. There are 12 records in our table, but from this query we just ran, we only see 8, so we know the query worked. Also, if you quickly scan down the CostPrice category, you should notice that all the values are greater than 90 cents.

To return to the Design window, click the Design button or select View, Design View from the menu.

Let's try another one. Now we are looking for all the products that cost between 2 and 3 dollars.

  1. Once in the Design View, delete the "<.9" criteria for CostPRice by highlighting the Criteria box and pressing the Delete key on the keyboard.
  2. In the the Criteria box in the CostPrice column, enter in "between 2 and 3". (yup, that's right, you do enter in the word "between")
  3. Run the query by clicking the Run button or select Query, Run from the menu.

You should only have two entries appearing:

Dealing with Text

We already dealt with handling a simple text query in the first example. This query works well if you are looking for exact values. What if we want to looking for a string inside the text (i.e. all entries that have the letter 'o' in them).

In these instances, we use the wildcard option. This uses a asterick. What this does is looks for zero or many characters in substitute Let's look for all entries that end with the letter 'e'. For this, we wan to look at all entries where there are no characters before or many characters before the 'e' on the end.

  1. Once in the Design View, delete the "Pop" criteria for Category by highlighting the Criteria box and pressing the Delete key on the keyboard.
  2. In the the Criteria box in the ProductName column, enter in "*e" (without quotations).
  3. Press the down arrow key on the keyboard. The "*e" turns to "Like *e". Access does this for you automatically. It converts it into the way that it needs to run the query.
  4. Run the query by clicking the Run button or select Query, Run from the menu.

You should get three rows returned in the dynaset:

To return to the Design window, click the Design button or select View, Design View from the menu.

Now let's look for entries where there is the letter 'o' anywhere in the text.

  1. Once in the Design View, delete the "ProductName" criteria for Category by highlighting the Criteria box and pressing the Delete key on the keyboard.
  2. In the the Criteria box in the ProductName column, enter in "*o*" (without quotations).
  3. Run the query by clicking the Run button or select Query, Run from the menu.

To return to the Design window, click the Design button or select View, Design View from the menu. Once in the Design View, delete the "ProductName" criteria for Category by highlighting the Criteria box and pressing the Delete key on the keyboard.

If you want to quit and take a break, close Access by clicking the button or select File, Exit from the menu. When Access asks you to save the query, click the No button.

[Back]

[Home]

[Forward]

edited by U.A.C Media