The Queries of a Database: Data Filtering

Introduction

Data filtering consists of isolating particular records based on a condition, also called a criterion or criteria. You start data filtering by selecting one or more columns that hold data you are interested in. This allows you to have access to all values of the table. Then, among the selected columns, instead using all available records, you specify a condition. Only the records that abide by the rule(s) you set would be produced. To specify a condition, you combine the SELECT expression with the WHERE operator. The basic formula to follow is:

SELECT What FROM WhatObject WHERE Expression;

The What factor is used to specify the column(s) whose data would be considered. The WhatObject is the name of the table (or query) that holds the data. The SELECT and the WHERE keywords are required. The Expression is the condition that will set the rule to follow. The Expression is a real expression. It can be made of the name of a column involved in a special conditional statement.

Consider the following list of registrants of a summer camp:

Imagine that, from this table of students, you want to extract only a list of male students, you would write the condition as Gender = Male. This would be done as follows:

SELECT * FROM Students WHERE Gender="Male";

When writing the name of a table, a query, or a column, if it's in more than one word, you must include it in square brackets. To be safe, even if the name is in one word, you should still include it in square brackets. Based on this, the above statement would be written as follows:

SELECT * FROM [Students] WHERE [Gender]="Male";

Another suggestion you can use is to qualify the name of each column to indicate the table or query it belongs to. To do this, type the name of the table or query, followed by a period, followed by the name of the column or the *. Here is an example:

SELECT Students.FirstName, Students.LastName
FROM Students WHERE Students.Gender="Male";

You can also delimit each name with square brackets as follows:

SELECT [Students].[FirstName], [Students].[LastName]
FROM [Students] WHERE [Students].[Gender]="Male";

To perform data filtering on a table, a query, or a form, after displaying the object, you can right-click the value under the column on which you will apply the condition and click Filter By Selection. In this case the equality of a WHERE condition would be applied. Based on this, the above WHERE Gender="Male" condition can be obtained by right-clicking a Male value under the Gender column and clicking Filter By Selection:

The opposite to the equality condition is the not equality. For example, instead of getting a list of male students as above, to get a list of students who are not male, you can write the condition as <>"Male" as follows:

SELECT * FROM Students WHERE Gender<>"Male";

To visually apply the not equality condition on a form, a query, or a report, you can click the value that would be excluded and click Filter Excluding Selection. The Filter By Selection and the Filter Excluding Selection options are used to apply the equality and the inequality conditions when filtering. Actually, a condition is created using the conditional operators we studied in Lesson 7 (but not exactly the conditional statements). To use them visually, right-click the table, the query, or the form, and use the text box of the Filter For option. In the box, type the expression including the column, the operator, and the value. Here is an example:

After typing the expression, press Enter and the condition would be applied. After a few examples of WHERE conditions on a table named Students:

To get a list of students who live in MD, you would use a statement like the following:

SELECT * FROM Students WHERE State='MD'

To get a list of girls with their names and email addresses, you would use a statement as follows:

SELECT LastName, FirstName, Gender, EmailAddress
FROM Students
WHERE Gender='Female'
ORDER BY LastName

To use the inequality operator, you can write the following statement:

SELECT FirstName, LastName, Gender
FROM Students
WHERE Gender <> 'Female';

To get a list of students born before June 1988, you would use a statement as follows:

SELECT FirstName, LastName, Gender, DOB
FROM Students
WHERE DOB < '1988-06-01';

To get a list of students born before 1990, you would use a statement as follows:
 

SELECT FirstName, LastName, DOB, EmailAddress FROM Students WHERE DOB <= '1990-01-01'

To get a list of female students by denying those who are male gender, you would write the following statement:

SELECT LastName, FirstName, Gender, DOB
FROM Students
WHERE NOT (Gender = 'Male')

To get a list of students where the record doesn't include an emergency name, you would use a statement as follows:

SELECT LastName, FirstName, Gender, EmergencyName
FROM Students
WHERE EmergencyName IS NULL

To get a list of the students whose records include a number to call in case of emergency, you could use the following statement:

SELECT LastName, FirstName, EmergencyName, EmergencyPhone
FROM Students
WHERE EmergencyPhone IS NOT NULL

After filtering data on a table, a query, or a form, you should reset the object. To do this, you can right-click anywhere in the object and click Remove Filter/Sort.

To programmatically perform data filtering on a table, a query, or a form, use its Filter property and assign it the WHERE condition. To apply the filter, access its FilterOn property and assign it a value of True.