Step Five - Advanced Criteria for a Query

We will now look at more advanced queries using and / or conditions. We will also look at Calculation Queries.

If you are coming back from a break, create a new query with the ProductName, CostPrice and Category fields.

OR Conditional Query

The OR condition allows us to select different sets of criteria for the same field. For example, we are looking for all products that have the letter 'b' or the letter 'k' anywhere in the text.

1. If you haven't done so already, delete any criteria that is there already.
2. In the the Criteria box in the ProductName column, enter in "*b* or *k*" (without quotations).
3. Press the down arrow key on the keyboard. The phrase turns to "Like "*b*" Or Like "*k*"". 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 have the following results:

AND Conditional Query

The AND condition allows us to set the criteria for the query based on multiple fields. For example, we are looking for all products that are in the Cookies category and more than $3.00.

1. If you haven't done so already, delete any criteria that is there already.
2. In the the Criteria box in the Category column, enter in "cookies" (without quotations).
3. In the the Criteria box in the CostPrice column, enter in "> 3" (without quotations).
4. Run the query by clicking the Run button or select Query, Run from the menu.

You should have the following results:

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

We can also have an AND within the criteria for one field. For example, we want all the products that have the letter 'o' and the letter 'e' in them. So, in order for them to show up in the the dynaset they have to have BOTH criteria to qualify.

1. If you haven't done so already, delete any criteria that is there already.
2. In the the Criteria box in the ProductName column, enter in "*e* and *o*" (without quotations).
3. Run the query by clicking the Run button or select Query, Run from the menu.

You should have the following results:

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

Calculation Query

A calculation query allows us to perform arithmetic and string calculations on the field in the query. Now why would you want to do this? Well, sometimes there is no need to store a fields in a table as it can be calculated by other fields that are stored in the table. For example, in a Wages table for employees, there is a field "HoursWorked" and another called "WageRate". Every month we need to get what the Gross Amount will be for their paycheque. It would make no sense to create a new field in the table design for Gross Amount as we already have the two fields that make up that field value. So in order to save space in the database, we would not create a new field in the table design, but we would do a calculation query to calculated the amount of Gross Amount by multiplying HoursWorked by WageRate. Eliminating these unnecessary fields is called normalization.

Let's do an example. We will calculate the amount of tax on our products.

1. If you haven't done so already, delete any criteria that is there already.
2. In one of the blank columns to the right, click once in the Field box.
3. This is optional. I find it to be quite a pain to type a lot information in that tiny box. While the blinking cursor is in the Field box, move the mouse pointer on top of the Field box and click the RIGHT mouse button. A pop-up menu will appear. Select Zoom from the list. The Zoom window will appear. This just makes it easier to type information in as the area and the font is larger.
4. Type in the following (an explanation will follow):

   TotalCost:[CostPrice] * 1.14

5. Click the OK button. The field box should look like this now:

   

6. Run the query by clicking the Run button or select Query, Run from the menu.
7. To return to the Design window, click the Design button or select View, Design View from the menu.

Now, what does this mean.

To close the query window, click the button or select File, Close from the menu. Click the No button when it asks to save query.

[Back]	[Home]	[Forward]

edited by U.A.C Media