Understanding Databases – Create a conditional query

Add criteria to a query using ‘NOT’ logical operator

4-19 Edit Query

From the Queries area of the main window, right click the query and click ‘Edit’.

4-20 Criterion NOT

In the Criterion row, add criteria that uses the ‘NOT’ logical operator, e.g. in Description field type “NOT=‘Home’”. This will return any data that has a description not equal to Home, i.e. Mobile

Also, select ascending from the ‘Sort’ row for the Group field to order the results
Press F5 to run the query, then F4 to close the preview.

Add criteria to a query using ‘AND’ logical operator

4-21 Criterion AND

In the Criterion row, add criteria that uses the ‘AND’ logical operator, e.g. in Group field type “<7 AND <>2”.

This will return any data that has a value less than 7 and not equal to 2

Add criteria to a query using ‘OR’ logical operator

4-22 Criterion OR

In the Criterion row, add criteria e.g. in the group field type =8. Then, in the ‘Or’ row below, add another criterion, e.g. < 5. This will return data for all group values that are equal to 8 or less than 5.

Use a wildcard in a query, * or %, ? or _

4-23 Criterion Wild

Selecting the wildcard asterisk * in the field column will return all the fields from the selected table.

4-24 Criterion LIKE

To run a query that searches for all Last Names beginning with J, in Criterion for Last Name field, type “LIKE J%”.

The % is a wildcard that stands for 0, 1 or more characters

In this case you could also type “LIKE J*” for the same result

Press F5 to run the query.

4-25 Criterion LIKE one

To run a query that searches for all First Names beginning with the letter ‘Bo’ with one character after them, in Criterion for Last Name field, type “LIKE Bo?” (or ‘_’ instead of ‘?’)

‘?’ or ‘_’ is a wildcard that stands for 1 character only.

Press F5 to run the query.