Access 101: SELECT Query

This article is part of my Quick Start Guide to Microsoft Access series, Access 101.

The SELECT query is one of the most fundamental and frequently used components of Access. This is the bread and butter of data retrieval, and it's where many of your interactions with your database will begin.

What is a SELECT Query?

In Access 101: What is a Query in Access? we learned that queries interact with data in four basic ways easily remembered by the acronym CRUD:

Create
Read
Update
Delete

Simply put, SELECT queries perform the Read function.

The Basic Structure of a SELECT Query

The basic structure of a SELECT query is as follows:

SELECT field1, field2, ..., fieldN
FROM tableName
WHERE condition
ORDER BY field1 ASC/DESC, field2 ASC/DESC, ..., fieldN ASC/DESC;

SELECT is the command that initiates the query.
field1, field2, ..., fieldN are the names of the fields (columns) you want to retrieve data from. If you want to select all fields, you can use * instead of listing them all out.
FROM tableName specifies the table from which you want to retrieve data.
WHERE condition is an optional clause that you can use to filter the data that's returned. The condition is a logical statement that can be used to filter records.
ORDER BY field ASC/DESC is an optional clause that you can use to sort the results. ASC sorts in ascending order, and DESC sorts in descending order. The ASC keyword is optional and generally omitted; if you don't include ASC or DESC then the field sorts in ascending order.

An Example SELECT Query

Let's say we have a table called Employee with fields EmployeeID, FirstName, LastName, and Position. If we want to retrieve all data from this table, we'd use the following query:

SELECT *
FROM Employee;

If we only want to retrieve the FirstName and LastName fields for employees who are managers, sorted by LastName in ascending order then FirstName in descending order, we'd use this query:

SELECT FirstName, LastName
FROM Employee
WHERE Position = 'Manager'
ORDER BY LastName, FirstName DESC;

Running a SELECT Query in Access

To run a SELECT query in Access, follow these steps:

Open your Access database and go to the "Create" tab on the Ribbon.
Click on "Query Design" to open a new query.
Using the "Add Tables" pane, double-click the table you want to query or click and drag it to the query window.
Double-click on the field names in the table in the top half of the query window to add them to the query.
Click "Run" (!) on the Design tab to execute the query.
Press [Ctrl] + [S] if you want to save the query for later.

Saved queries can be used as the record source for forms, reports, and certain controls, like combo boxes and list boxes.

SELECT Queries from an Excel Perspective

Many first-time Access users arrive at the application because they've pushed Excel as far as it can go and are frustrated with its limitations. This section of the article is just for you.

The WHERE Clause: Filters in Excel vs. Access

In Excel, you're likely familiar with the concept of filtering. You set conditions on your data, and Excel hides the rows that don't meet those conditions. It's a powerful tool for focusing on specific subsets of your data.

The WHERE clause in an Access SELECT query serves a similar purpose. It allows you to specify conditions that the data must meet to be included in your query results as demonstrated in the example SELECT query above.

However, there's a key difference between filtering in Excel and using a WHERE clause in Access.

In Excel, when you apply a filter, you're temporarily hiding rows in the interface, but the data itself is still there. In Access, a WHERE clause doesn't hide rows - it actually determines which rows are retrieved from the database. The data that doesn't meet your conditions isn't just hidden; it's not included in the query results at all.

The ORDER BY Clause: Sorting in Excel vs. Access

Sorting is another common operation in Excel. You can sort your data based on one or more columns, in ascending or descending order.

The ORDER BY clause in an Access SELECT query does the same thing. In the example SELECT query above, the employees are sorted by last name then first name.

Again, though, there's a crucial difference between sorting in Excel and using an ORDER BY clause in Access. In Excel, when you sort your data, you're rearranging the rows in your worksheet. In Access, an ORDER BY clause doesn't rearrange any data in your tables. Instead, it determines the order in which the data is presented in your query results.

Another difference is that you can sort by fields that are not included in the result set. For example, you could sort by Position even if you only displayed the employee's names, as shown here:

SELECT FirstName, LastName
FROM Employee
ORDER BY Position;

IMPORTANT NOTE: If you do not specify a sort order, there is no guarantee what order the records will be returned to you. In theory, they could be returned in a different order each time. In practice, the order tends to remain consistent. However, if the order of the records is important, be sure to include an ORDER BY clause.

The Power of SELECT Queries Over Excel Workbooks

This brings us to one of the most powerful aspects of SELECT queries in Access: they allow you to create and save multiple views of your data, without altering the underlying tables.

In Excel, if you want to view your data in different ways, you often have to create multiple copies of your worksheet, each with different filters and sorts applied. This can quickly become unwieldy, especially with large datasets.

In Access, you can create as many SELECT queries as you want, each with its own WHERE and ORDER BY clauses, and each presenting a different view of your data. You're not duplicating data, and you're not altering your tables. You're simply defining different ways to retrieve and view the data.

This can be incredibly liberating for data analysts. Instead of wrestling with multiple copies of your data, you can focus on defining the views that you need, safe in the knowledge that your underlying data remains consistent and unaltered.

Conclusion

The SELECT query is a powerful tool for retrieving data in Access.

By specifying the fields and tables you're interested in, and optionally applying conditions to filter your results, you can pull exactly the data you need. The ORDER BY clause further enhances your control by allowing you to sort the results.

And the best part? You get all this power without having to maintain duplicate copies of the underlying data.