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).
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.