Making a query in MS Access allows you to do a multitude of things with your data and database. The most common purposes of a query are to:
- Search and filter your data to find something specific
- Aggregate and Summarize data
- Automate processes
The simplest of queries to make is the Select Query. The select query, as it’s name indicates, allows you to select/retrieve data from your table(s). You can select specific fields, and specific records that meet your filters criteria that you define.
In our prior posts, we were working with tables that contained Employee Hours and Employee Addresses. Let’s make a simple Select query on the Employee Hours table.
- Go to the Create Tab
- Choose Query Design
- Select the table tbl_employee_hours from the Tables Tab
- Click Add
- Click Close
You can see our table is now shown within our query as well as the field names within the table; however, trying to run the query right now will result in an error from Access because we haven’t told the query what to select yet. All we have told it so far is that we want “it” to be from this employee hours table.
To add all of these fields to the table, double click on the Asterisk at the top. The asterisk at the top means “All” in Access and in SQL programming language. Adding the asterisk to your query will add all fields within this table to your query.
Click the view or Run icons in the top left to run your query on this table.
What’s the point, it’s just our table
You might be asking yourself what the point of this query is and honestly, in it’s current state, none. The query provides the exact same data results as what is in our table so let’s change that.
Go back into design view on your query and remove the field tbl_employee_hours.*
You can do this by hovering your mouse cursor just barely above the field name until it shows as a black down arrow. Left clicking will select the field and pressing the delete key will remove it.
This time, I want to add only the fields Name, Hours, and EMPLID.
- Double Click on Name
- Double Click on Hours
- Click and drag-and-drop the field EMPLID into the first field/column slot. I want EMPLID to be the first field in my table and doing this will bump the other two fields over for me.
- View/Run your query.
Now we have only the data we want to see.
Lets go back to design mode.
Notice the row descriptions for the fields we added
- Field – Displays the field name from the table. Can also display expressions (formulas)
- Table – Displays the Table the field is from
- Sort – Allows you to sort the field
- Show – Allows you to show or hide a field or expression
- Criteria/Or – Allows you to set filters for your Data
For the last bit of this Select Query, let’s add in a filter to only see the record for John Doe. John’s employee ID is 12345. Add that to the Criteria for the EMPLID field and run your query.
Our query is now only showing John’s record.
This concludes the introduction to Queries and Select Queries. We will continue to dive into further detail on Select Queries and all facets of MS Access very soon.