databasedev.co.uk - database solutions and downloads for microsoft access

SQL WHERE Clause

Use of the Where Clause in Microsoft Access:

In order to write effective Microsoft Access queries, you'll need to master the SQL WHERE clause. Simply put, the WHERE clause allows you to limit the results of your query based on conditions that you apply.

Throughout the tutorial, we'll refer to the following table, EMPLOYEES.

ID Last Name Department Salary
01 Smith Janet 10 20000
02 Walters Ed 10 30000
03 McGee Keith 20 28000
04 Breedling Alice 30 29000
05 Jones Brenda 30 32000

In all examples, we'll be working in Microsoft Access SQL view. This view allows you to type in your SQL without using the QBE (Query by Example) Design View. When you've entered the query, you can run it by clicking the red exclamation mark in the toolbar (or click "Query" and "Run" from the menu).

To access SQL view:

  1. Click "Query" and "New".
  2. When the table selection opens, select the table by clicking on it and click "Add".
  3. Click "Close".
  4. In the top left corner of the window (beneath "File"), click the small arrow and select "SQL View".
  5. A window will open where you will type your queries.

Anatomy of an SQL Statement

SQL, Structured Query Language, is the language that you use to write queries in most databases, including Microsoft Access. Most SQL statements have three basic parts: the SELECT, FROM and WHERE clauses.

The SELECT portion of the SQL statement lists what fields you want displayed in the results. In many cases you'll use the asterisk (*) to denote that you want all fields shown. Alternatively, you can list each field in the order you want them displayed.

The FROM clause lists the table or tables from which you'll retrieve data. The WHERE clause will allow you to limit the results based on specific conditions.

By leaving the WHERE clause out of our statement, we automatically select all records available. We can do so from the table EMPLOYEES by entering:

Select *
from EMPLOYEES

This query will list all the records in the table because we did not specify any conditions on what we wanted returned. However, you probably would prefer to select only a part of the records, especially if the table contains a lot of data. The WHERE clause will allow you to accomplish this.

Example 1: Select all employees from department 10.

Enter the following in SQL view:

Select *
from EMPLOYEES
where department = "10"

Only two records will be retrieved since only two correspond to the conditions set in the WHERE clause.

ID Last Name Department Salary
01 Smith Janet 10 20000
02 Walters Ed 10 30000

Example 2: Using multiple conditions

You can use more than one criteria in a query using the "AND" and "OR" logical operators. These allow you to logically combine conditions to further limit or specify the rows you want returned.

For example, you may want to select all employees who are in department 10 and who have a salary over 25000.

In SQL view, the following query will accomplish this:

Select *
from EMLOYEES
where department = “10”
and salary > 25000

In this case you'll only see Ed Walters returned since he is the only one who now meets our criteria for selection:

ID Last Name Department Salary
02 Walters Ed 10 30000

Example 3: Using an OR to connect multiple conditions

You can also join criteria with an "OR". To accomplish this, do the following:

Select *
from EMLOYEES
where department = “10”
or salary > 25000

In this case, you'll see all five employees because they each satisfy at least one condition.

ID Last Name Department Salary
01 Smith Janet 10 20000
02 Walters Ed 10 30000
03 McGee Keith 20 28000
04 Breedling Alice 30 29000
05 Jones Brenda 30 32000

When using "OR", only one condition needs to be satisfied in order for a record to be selected. When using "AND", all conditions must be satisfied in order for a record to be selected.

Example 5: Using wildcards

Sometimes you'll need to use wildcards to approximate the data. For example, you may be unsure of a spelling, and using wildcards can help you find what you're looking for.

For example, let's say you aren't sure if Alice's last name is Breedling or Breedlove. You can still find her in the database by using the following query:

Select *
from EMLOYEES
where Last like "Breedl*"

This query will return any employee whose last name begins with "Breedl". So Alice will be selected.

ID Last Name Department Salary
04 Breedling Alice 30 29000

After mastering the basic syntax of SQL statements, including the various uses of the where clause, you can more fully take advantage of the power of Access.