Databases

Queries (2)

There are three parts to making a new query:-

1. Decide which fields you need in the query
2. Decide what criteria you are going to apply to the selection
3. Decide if you are going to sort the result in any way

1. Decide which fields you need in the query

At this stage we have selected four fields from the table (tbllessons). You can see that they appear in the bottom half of the query design window.

2. Decide what criteria you are going to apply to the selection

There are many kinds of criteria that we could choose. In this example we have types in a direct value, and the query will only display results which have the homework field set to yes.

We can also use a formula as a criteria, for example if we only wanted lessons that were during week 2, we could use ">5" as the criteria in the "Day" field. This would give us days 6,7,8,9 and 10, which is just what we needed. We can also use "<" to give us values which are lower than. The criteria that we use are actually very similar to the validation rules that we used in the validation rule section (click for a recap)

We can use more than one criteria to search our database and it is perfectly possible to find out what homework we have on day three by using criteria on the Day field and the Homework field.

3. Decide if you are going to sort the result in any way

Our homework query didn't try to sort the data in any way, because the data was actually entered into the database in the correct order. This isn't always the case, but Access has a method of allowing the data to be sorted into any order we require.

If we had wanted our homework query sorted into order so that the days appeared in numerical order, we could have used the drop down menu under the Day field to select an "ascending" order of sort.

As you can see, we can also choose to sort a field into descending order so that the results are displayed from the highest value down to the lowest.

Just like the criteria can be applied to more than one field, we can sort the results by using the values stored in more than one column. The sort is applied to the results from the left hand column to the right, so if "Day" appeared to the left of "lesson" and they were both sorted ascending, then the days would all appear together rising from lowest to highest, followed by the lessons in order.

It is also possible to use "or" criteria, but we will look at those in more detail later.

Before we move onto Forms, Access Queries have one more powerful trick up there sleeve.

Click here to find out how Aggregate Queries work.

Click here to return to the top of the page