Access 101: SELECT Query

The SELECT query is the source of a relational database system's true power.

Mike Wolfe

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:

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:

1. Open your Access database and go to the "Create" tab on the Ribbon.
2. Click on "Query Design" to open a new query.
3. Using the "Add Tables" pane, double-click the table you want to query or click and drag it to the query window.
4. Double-click on the field names in the table in the top half of the query window to add them to the query.
5. Click "Run" (!) on the Design tab to execute the query.
6. 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.

Further Reading

Acknowledgements

• Initial draft generated with the help of ChatGPT
• Portions of this article's body generated with the help of ChatGPT
• One or more code samples generated with the help of ChatGPT

Never miss an article.

Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week.
(I'll never sell your email. Unsubscribe any time.)

← Next Article

Previous Article →

Automating Temporary Data Cleanup in Access with the ClearTempTables() Procedure

The ClearTempTables() procedure is a quick way to empty out temporary tables in your front-end Access files before deploying updates.

Report Builder: The Reports & Templates Tables

A description of the tables used to store built-in and user-created report templates for use with my Advanced Report Builder.

Dark Mode All the Things

If exposure to excessive light will render your undead body a pile of ash--or you simply want to reduce eye strain--this app-by-app list of Dark Mode settings is just what you need.

Throwback Thursday: September 5, 2024

How do non-programmers become Access developers? We explore a common career arc in today's edition of Throwback Thursday.

I Paid $600,000 per Hour for LASIK Eye Surgery

And that's why ophthalmologists don't bill by the hour.