Creating Queries in Access

Queries allow you to ask questions of your data. We might ask Access to show us all the faculty in a certain department, or all faculty who were hired on or after a given date. This is where the power of databases becomes apparent, as we can slice and dice our data to find out very specific information. We can analyze our data and find trends by asking the right questions of our data.

When running a query, Access will retrieve and display a set of records in datasheet view, which is called a dynaset. This reflects that the display is dynamic and could change each time the query is run, depending on what data has been added, edited, or deleted.

Creating a Single Table Query

We'll start with a simple one-table select query from tblFaculty, and then see how to build a relational query by connecting faculty and their departments, pulling fields from both tblFaculty and tblDepartments. A select query is a query that displays subsets of data but does not perform any action on them. To start making a query, we'll want to switch to the Create tab on the Ribbon.

Step1. To switch to the Create tab, on the Ribbon,

Click the Create command tab

There are two options in the Queries group of the Create tab:

The Query Wizard can be useful for more advanced queries, but we'll start with the Query Design tool, as it gives us the chance to learn more about how queries work, as well as giving us more control.

Step2. To open the Query Design tool,

Click

The Query Designer workspace appears - this is where we will assemble the query. The Add Tables pane also appears, which we saw earlier when creating relationships between tables.

Selecting a Table for a Query

We will open tblFaculty to start with, as all the fields we need for our first query are in that table.

Step1. To open up tblFaculty, in the Add Tables pane,

Double-Click tblFaculty

We now see a window for tblFaculty in the Query Designer workspace:

As we saw earlier in the relationships workspace, the window for tblFaculty is not long enough for us to see all the fields. Let's fix that first.

Step2. Expand the tblFaculty window, if necessary, so that all of the fields are displayed.

Selecting the Appropriate Fields

For our first query, we'd like a list of all faculty members with their departments and rank.

Step1. To add the first field to the query, in the tblFaculty window,

Double-Click LastName

LastName is listed as the first field in the query design grid. Note that once it was added, the Show checkbox for that field was selected. This means that the query will display the LastName field in the datasheet view.

Step2. To add the next field,

Double-Click FirstName

The FirstName field is added to the right of LastName in the bottom half of the screen.

Step3. To add the last two fields to the query,

Double-Click DeptCode, Double-Click Rank

Now we have four fields listed in the query design grid.

We'd like to see the results of this query. To do so, all we need to do is change to datasheet view.

Step4. To switch to datasheet view,

Click

We are switched to datasheet view, which shows us the results of our query.

NOTE: Although there is a Run option on the Design tab on the Ribbon, simply viewing our results in datasheet view is sufficient for this query. In some cases, Run is used to make changes to the data, but it's always safest to look at the results using datasheet view first.

This query, while it only brings in fields from tblFaculty, does not show all fields from that table. We can use queries in this way to help us filter information in our database that we might not need to see at this time. Also, note that the default sort order is still by the primary key, in this case, the EmployeeID. Since EmployeeID is not visible, and we did not specify any other sort order, our results look somewhat randomly organized.

What we want to do next is sort the output so that faculty are listed in alphabetical order.

Sorting the Query Output

The default display of our initial query is by EmployeeID number, but that is not particularly useful in this case. We'd like to have our faculty listed alphabetically by name, as that order is easier to understand at a glance.

We'll sort by last name and then first name. We'll have to switch back to design view first.

Step1. To switch back to design view,

Click

If we observe the query design grid closely, we see several options below each field:

We'll make a change to the Sort field to affect the order of our output.

Step2. To sort the output by last name, under the LastName column,

Click the Sort field

The Sort field is now active, and we can see a drop-down menu arrow.

Step3. To select the desired sort option, in the Sort field,

Click, Click Ascending, press: Tab key

The sort field for LastName is set to ascending, and the cursor is in the sort field for FirstName.

An ascending sort order will give us a normal alphabetical order. Descending would be reverse alphabetical order, or, if the value were numeric, largest to smallest.

If two faculty have the same last name, their first name will still appear in order by the table's primary key. We'd like faculty with the same last name to be further sorted alphabetically by first name.

Step4. To select the desired sort option,

Click, Click Ascending

When there is more than one field that is sorted, Access looks at each listed field from left to right. Thus, our query output will now be sorted by the LastName field first, and then if there are any matches by last name, they will be sorted next by FirstName.

Let's look at our query output again to see our changes take effect.

Step5. To return to datasheet view,

Click

Now our records are sorted by LastName and then by FirstName. Note that faculty that have the same last name are sorted alphabetically by first name.

Next, we'll look at using the Criteria field to filter our query output results.

Using Criteria to Filter Query Results

Suppose we wanted to focus on the faculty of the Political Science department. We could sort our records by department code and then scroll down to the relevant section, but it would be even easier if we could limit our query output to just display the relevant records.

We'll make use of the Criteria field for the DeptCode field in the query design grid to filter our results.

Step1. Return to design view.

Step2. In the query design grid, under DeptCode,

Click in the Criteria field

If there's a specific value we're looking for, we can simply type that here.

Step3. To limit our results to faculty in the Political Science department, type:

POLS

NOTE: Entering the department code in lowercase letters will return the same results.

Step4. To view the results,

Click

Now our output only includes faculty in the Political Science department. Note that the sort order by LastName and FirstName is preserved.

Editing Values in a Query

Our dynaset may resemble a new table, but what we're actually seeing here are the values in the underlying table (in this case, tblFaculty). This means that while we are viewing our output, we could make changes to the data in the dynaset, and this change would then be saved in tblFaculty.

Let's see this in action by editing a faculty member's name. We have learned that Billy Black would prefer to go by his full first name, William.

Step1. To edit Billy Black's first name, in the fifth row of the dynaset,

Click Billy

Step2. To change the value,

Press & Drag across "Billy," type: William Down Arrow key

NOTE: Check that the edit icon is no longer next to the record to ensure that your changes have been saved.

Let's check to see if that value was changed in the underlying table.

Step3. To open tblFaculty,

Double-Click tblFaculty

tblFaculty opens in a new tab, and we can see in the fourth record that Professor Black's first name is "William."

Note that queries will always show you the most current information when you run them. If we make changes to the data in tblFaculty and then return to the query, those changes will be reflected in the output.

Let's try that now by making a change to our underlying data in the table. We'll make a correction to Brian Briones's first name.

Step4. To edit Brian Briones's record, in the fifth row in the FirstName field,

Click Brian

Step5. To change the first name to Bill,

Press & Drag across "Brian," type: Bill Down Arrow key

Step6. To return to the query, at the top of the object workspace,

Click Query1

Step7. Scroll down, if necessary, to find the last name "Briones" in the query output.

We see that the FirstName field already reflects the new entry, Bill. Let's save this query so that we can run it again in the future.

Step8. To begin saving, in the Quick Access toolbar,

Click

Because this is the first time we're saving this query, the Save As dialog launches automatically. We'll use the Access naming conventions for naming queries, "qry," as a prefix.

Step9. To name the query, in the Query Name field, type:

qryPOLSFaculty Enter key

Next, we'll create a relational query and take advantage of the relationships we've set up in our database.

Creating a Relational Query

Now that we've seen how to create a simple, one-table query, let's try creating a more complex query that includes information from tblDepartments, and lists all faculty members by their departments.

We're going to base our new query on what we've done so far, but before we start making changes, we should save the query with a new name. This way, we don't risk overwriting the current query.

Saving a Query with a Different Name

It's a very common thing to adjust an existing query to ask a related but slightly different question of the data, in this case, to list all faculty with the full department name. However, we don't want to lose the structure of the original query that we built. Instead, we will come up with a new name for this query, and save it as a different database object.

It would be helpful to have the shortcut to Save As in our Quick Access toolbar in the upper-left corner of the program. As with most other Microsoft Office programs, the default tools on the toolbar are just Save, Undo, and Redo. However, Microsoft makes it pretty easy to add additional tools to the Quick Access toolbar.

Adding a Shortcut Tool to the Toolbar

We'll add Save As to the Quick Access toolbar in the upper left, so that if we decide to tweak other database objects, we'll have an easy way to save those objects with a new name.

Step1. At the top of the object workspace, click the qryPOLSFaculty tab, if necessary.

Step2. To start adding a new tool to the toolbar, in the upper-left corner of the Access window,

Click

The Customize Quick Access toolbar drop-down menu appears, with a few common tools that can be added to the toolbar. Save As, however, is not among them, so we will need to launch the Access Options window to find it.

Step3. To search for additional commands,

Click More Commands...

The Access Options dialog box opens, and we see a list of popular commands to choose from, listed in alphabetical order:

Step4. To choose Save As, in the list on the left,

scroll down if necessary, Click Save As

Step5. To move Save As to the Quick Access toolbar list on the right,

Click

Save As is now added to the list of tools on the right, just below Save.

Step6. To accept the changes, in the bottom right corner of the Access Options dialog box,

Click

The Access Options window closes, and we see the Save As icon in the Quick Access toolbar. Let's save our new query now.

Step7. To save our query with a new name, in the Quick Access toolbar,

Click

The Save As dialog box opens:

Access allows us to change the name, and also to change the type of database object. We'll just rename our query.

Step8. To rename the query, in the upper field, type:

qryAllFacultyByDept, Click

Our new query is saved, and appears in the navigation pane in the Query group, right above qryPOLSFaculty.

Adding Another Table to a Query

Now that we've saved our query with a name that describes what we want it to do, we'll need to add tblDepartments to our Query Design workspace so that we can add the necessary fields.

Step1. To begin, switch to design view.

Step2. To add another table to the Query Design workspace, in the Add Tables pane,

Double-Click tblDepartments

tblDepartments is added to the workspace. Notice the one-to-many relationship is already defined in the Query Designer; this is because it has been defined for the entire database.

Step3. Expand the window for tblDepartments, if necessary, to see all the fields.

Due to the way we added our tables, tblFaculty is currently on the left, but we can change the way the windows are displayed without affecting how Access understands the relationship. Let's move tblFaculty to the right side of tblDepartments. We can easily drag and drop the windows to move them around the workspace by using the Title bar of the window.

Step5. To move tblFaculty in the workspace,

Press & Drag to the right of tblDepartments

Your screen should look similar to the following image:

Note that the direction of the one-to-many line continues to reflect the correct relationship, one on the side of tblDepartments and many on the side of tblFaculty. Although changing the layout does not change the relationship in any way, it might make it easier for us to grasp the direction of the relationship at a glance.

Adding and Removing Fields from a Query

We would like to add the full department name to our query, rather than just listing the department code. We could double-click DeptName from tblDepartments to add this field to the end of our query, but we'd like to list that first, so we'll use a different method to add it.

Step1. To add DeptName to the beginning of our query,

Press & Drag DeptName to the first column of the query design grid

The DeptName field is now inserted at the beginning of our query, to the left of our previous first field, LastName.

Now that we have the full department name listed, let's remove the criteria for DeptCode so that all departments will be listed.

Step2. To remove the criteria from the DeptCode column, in the query design grid,

Press & Drag across "POLS," press: Delete key

Let's view our output with the new field added.

Step3. Switch to datasheet view.

The first field in the display is the department name, but the records are still sorted alphabetically by faculty last name, not by department. Also, now that we have DeptName in our query, we really don't need to have DeptCode too.

Let's go back to design view to fix these two things.

Step4. Switch to design view.

First, let's eliminate the DeptCode field from our query. In order to select the DeptCode field in the grid, we need to hover over a very slim bar at the very top of the DeptCode column. When we do, we'll see the cursor change to a down arrow:

Step5. To select the DeptCode field, in the Query Design grid,

Click

Step6. To delete the field, with the entire field's column selected, press:

Delete key

The DeptCode field has been removed from our query.

Now we'll see if we can determine why our output is not sorted by department. Notice that there is no ascending sort applied to the DeptName field; the first sort is in the LastName field. We'll add a sort for the DeptName field.

Step7. To sort alphabetically by department name, in the Sort field for the DeptName column, type:

a

The option for ascending automatically populates, without requiring us to press enter or tab away. Let's see how this affects our listing order.

Step8. Switch to datasheet view.

Step9. Scroll down to see the full sort, if needed.

Now we see the Biology department faculty listed first, in alphabetical order, followed by the Mathematics faculty, the Philosophy faculty, and Political Science. We're happy with our query changes, so let's save them now.

Step10. Save the query.

Comparing Query Results with the Underlying Data

As we look at our results, there seems to be a department missing from our list. In our tblDepartments, there are actually five records. Let's open tblDepartments to confirm this.

Step1. Open tblDepartments from the navigation pane.

If we look carefully, we'll see that the missing department is Anthropology. To determine why that is, we'll take a look at tblFaculty and search for Anthropology.

Step2. Click the tblFaculty tab at the top of the object workspace.

Step3. To open the Find dialog box, press:

Control key + F

Step4. To search for the Anthropology department, in the Find Next field, type:

anth Enter key

Access indicates that there are no matches. The Anthropology department is brand new, and our database does not have any faculty listed for it yet.

Step5. To close the Find dialog box,

Click, Click

It's important to note that although our query lists the field DeptName from tblDepartments, this does not automatically mean every value from that field will display in our query results; because this is a relational query, Access will only display matching records, that is, records that match from both tables. To learn more about relationship properties, take the course Access: Structuring and Relating Data.

There are ways to configure the query so that all departments would be listed, whether or not there were faculty in that department, but in the current configuration, which is considered a standard select query in Access, it will only show records that match from both tables.

Next, we'll explore some of the other possibilities we have when using criteria in a query.

Adding Additional Criteria to a Query

Suppose we'd like to retrieve a list of faculty that were hired after a specific date, and earn above $70,000. In addition, we'd like to sort our results by rank. We'll add some fields to our query, and use criteria to limit what records are returned.

Before we adjust our existing query, let's use Save As again, so that we don't accidentally overwrite the structure of qryAllFacultyByDept before giving it a new name.

Step1. Return to the qryAllFacultyByDept tab.

Before we save our query with a new name and start making changes, we should make sure we've preserved the changes we've made to our current query.

Step2. To save the query settings with its current name, press:

Control key + S

Step3. To save the query with a new name, in the Quick Access toolbar,

Click

Step4. To rename the query, in the upper field, type:

qrySalariesByRank>70,000Hired2000OrLater, Click

This query name may seem extraordinarily specific, but in general, it's good practice to give queries (and other database objects) names that make it very clear what their purpose is. That way, when we return to our database after some time has passed, or if we give the database to someone else to manage, it is very obvious when and why someone might choose to run the query again.

Let's start modifying our query now to get the results we're looking for.

Step5. Return to design view, if necessary.

Step6. To add the additional fields we need to the query design grid, in the tblFaculty window in the query designer,

Double-Click Salary, Double-Click HireDate

The two fields are added to the query:

We'll start by limiting our results to faculty who are earning more than $70,000.

Step7. To set the criteria, in the Criteria field of the Salary column, type:

>70000 Tab key

Our cursor is now in the Criteria field for HireDate. Since we also want to limit our results to faculty who were hired on or after January 1st, 2000, let's enter that criteria now.

Step8. In the Criteria field of the HireDate column, type:

>=1/1/2000

NOTE: Access treats dates as numbers; this allows us to use mathematical operators such as < or > or = with dates.

Adding the equals sign after the greater than sign will include anyone whose hire date is January 1st, 2000, in addition to those who were hired after that date.

Now let's take a look at our output.

Step9. Click the View button to return to datasheet view and see the results.

We see our results have changed; now, in the record navigation, we show just 32 faculty members. Our query is now an AND query, because we have two different criteria listed on the same row. This requires that both conditions to be true. The query will only list faculty who earn more than $70,000 whose hire date occurred on or after January 1st, 2000.

Currently our results are sorted by department, but we'd like to group our results by rank. We'll fix that now.

Step10. Click the View button to return to design view.

Right now our results are being sorted on multiple fields, including LastName, FirstName, and DeptName. We'd like to sort first by rank, and then by our other fields.

Reordering the Fields

Remember that Access performs multiple sorts in order from left to right. If we add a sorting by rank in the current location, we will not get the order we're looking for. We need to move Rank to the beginning of the query design grid.

Step1. To select the Rank field in the query design grid,

Click

Step2. To move the Rank column before the DeptName column, with the Rank column selected,

Press & Drag the Rank column before the first column on the left

As we drag the column to the right, we'll see a dark line indicating where the column will be inserted:

When we let go of our mouse button, the Rank column replaces DeptName at the beginning of the query design grid, and the other columns shift to the right. Now that Rank is our first field, we'll sort on that.

Step3. To sort on Rank,

Click in the Sort row of the Rank column, type: a

The Rank field will now be sorted first, in alphabetical order. Let's see how our results have changed.

Step4. Click View to return to datasheet view.

Now our results are sorted, with associate professors listed first by department (and then alphabetically by name), then full professors. We can get a sense from looking at the data that in general, salaries increase when faculty move up in rank.

For this particular query, the exact hire date is not important to see in the results. We can't remove the field completely, as we're limiting results based on HireDate, but we can hide it from the output. Let's do that now.

Step5. Return to design view.

Step6. To hide the hire date, in the HireDate column of the grid,

Click the Show checkbox

The Show checkbox is deselected for HireDate.

Step7. Return to datasheet view.

The HireDate field is no longer visible in our results.

Step8. Save the query.

We're done with our query. We don't need any of the other objects open, either, so we'll close all of them at once.

Step9. To close all open objects, at the top of the object workspace,

Right-Click the tblFaculty tab, Click Close All

To learn more about creating queries, consider taking the course Access: Analyzing and Modfying Data using Queries.

Now, let's create a form for our data.