Working with databases/Database queries/Modifying queries

Working with Databases

Basic skills applicable to planning, creating and using an Access database.
An OP Online course from the National Certificate in Computing (Level 2) programme.

About this course  |  Getting started  |  Course schedule  |  Assessment overview  |  Help

Editing your Query – adding Criteria

We have successfully created and saved a query in our database. At the moment our query shows all books in our database, but we are going to refine this to only show paperbacks. To do this we need to go Query Design View. Once there we are going to add a criteria to our query – this will allow us to fine tune what the query displays.

Activity

Open your database. From the Objects pane open your query From the Views group, you may find that design view is already showing. If not, click on the drop down arrow and choose Design view. Save your database before you move on

Adding criteria to queries

Criteria are used to help refine what we are looking for and are applied to a particular field. For instance we could have a criteria to display only paperback books.

The type of criteria has to match the type of field. So for instance, specifying a criteria of More than $5 in a field that is set up as text and is labeled Surnames would make no sense to the computer and you would get an error.

 

Check your understanding

• To which field would we apply the criteria of Surname = Smith?
  • First name
    • No, that's not correct
  • Surname
    • Yes, well done!
  • Address
    • No, that's not correct
• To which field would we apply the criteria of Phone number starts with 65?
  • Surname
    • No, that's not correct
  • Address
    • No, that's not correct
  • Phone
    • Yes, well done!

Boolean operators

In order to actually apply a criteria we use Boolean Operators. Boolean operators are a shorthand way of saying things like “greater than...” or “equals...” or “everything except...”

Here are some examples that we might use:

Criteria for Numbers

> 12

The query will display results greater than 12

<3

The query will display results less than 3

<>5

Results that do not equal 5 will be displayed

= 10

Results that equal 10 will be displayed

>= 3

Results greater than or equal to 3 will be displayed

<= 3

Results smaller than or equal to 3 will be displayed

Criteria for Text

“Steve”

Only the fields that contain the exact text between the quote (“ ”) marks will be displayed. In this example, only fields with the word Steve will be displayed.

The wildcard character, the asterisk or star - *

This can be used to substitute for one to many letters. For example “St*” in a query could find Steve, Stewie, Stuart, Stephen.

“B*g”

Could find Bring, Being, Boing, Belonging, Blustering etc.

Activity

You will not need to open your database for this. On a sheet of paper, write down the criteria that you would use to find the following. The first few examples are given for you: When you have finished, check your work against our model answers.

Activity

We are going to add a criteria to show only books that are paperbacks. Open your database. From the navigation pane, open your query From the views group, you may find that design view is already showing. Otherwise click on the drop down arrow and choose design view. Locate the field called Paperback Book. This is where we are going to apply the criteria to. You will also see a row called criteria: Click your mouse where the criteria row intersects with the Paperback Book field column - this is where we will enter our criteria values. You will see the flashing text cursor. If you remember back to when we designed this field, we designated this as a Yes/No field which is displayed as a box with a tick for “Yes” and an empty box with no tick, for “No”. Our criteria is: Yes, so enter this value in now. Run your criteria by clicking on the Run icon. You should see the following: Here we can see books that we have indicated as paperbacks are displayed. Save your database and close your query. Open your table and change the following two books to be paperbacks: Secrets of Fly Fishing World's Greatest Love Stories Save your database and reopen your query – what has changed? You will find that queries do not automatically update as you change your table data – you may need to close and re-open queries to see the changes in your table take effect.

|