Working with databases/Database queries/Modifying queries
|Working with databases|
|Database queries||Introduction | Simple queries | Modifying queries | Advanced queries | Key points | Assessment|
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.
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.
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
- The query will display results less than 3
- 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
- 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.
- Could find Bring, Being, Boing, Belonging, Blustering etc.