Find us on GitHub

Teaching basic lab skills
for research computing

Using Access: Filtering

Using Access/Filtering at YouTube

Welcome to the Software Carpentry screencast on Databases using Microsoft Access. This screencast is on filtering results from a databse.

As we've seen in a previous screencast, we can use the simple queries to pull out columns from a table, and add computed values to results. One of the most powerful features of a database is the ability to filter through your data to find the records that match certain criteria.

Let's say we wanted to see all of the Experiments before the year 1990. To do this we'd write a query with all of the columns from the Experiments table, and use the criteria row to specify our filter conditions. To do this, we add the criteria "< 01/01/1990" to the ExperimentDate column. This specifies that we want only experiment info with an experiment date before January 1st, 1990.

We can think about how this query works as the database inspecting each row from the Experiments table, and checking it against the condition in the criteria. If the condition holds true, then that row is included in the results, otherwise it is filtered out.

We can use other comparison operators in our filtering conditions. For example, we could ask for all of the Experiments in table that took 3 or more hours. To do this we remove the criteria from ExperimentDate and add the criteria ">= 3" to the Hours column. We can use most of the familiar comparision operators: equal to, not equal to, greater than, less than, and so on.

We can make our filters even more sophisticated by combining criteria from multiple columns.For instance, if we want to find all of the experiments run by mlom that took more than three hours, we can modify our query. It already filters for experiments that take more than 3 hours. So, we can add the additional criteria that needs to be met, that the loginID is mlom. Including criteria in the same row is like saying, AND. Each record returned now must satisfy two conditions: in this case, the Hours spent has to be greater than 3, and the loginID must be "mlom".

If we wanted experiments either by mlom or by best, we could write this into the criteria row by using the keyword OR. We could also do this by listing the criteria for each column, one per row. But this can be confusing because the database checks the criteria for each row all on its own, so if we don't copy all of the criteria we want on to each row we may get unexpected results.

Another way to write this OR-clause is to use the IN operator. With this operator, we can write a condition that is true if a field -- the loginID in this case -- contains anything from a list of values. So we'd write the IN and then provide the list of values "mlom", and "best" in parentheses: IN ("mlom", "best"). This is exactly the same as writing out the OR clause, but it's shorter to write, and easier to read.

In this screencast we've shown that you can select data from a table and then filter those rows based on TRUE and FALSE conditions using the criteria row.