Modifying a Query in Microsoft Access
Congratulations! Our company's purchasing department was very pleased with the results of the query you designed for them after last week's tutorial. However, the sheer amount of data the query produced overwhelmed their staff and they've come back asking you to provide them with more concise results.
Last week's query displayed the inventory levels for all of the products in our inventory. In order to satisfy our customer, we'd like to add several features to our previous query. First, we'd only like to display those products where our current inventory level is less than ten with no products on order. Second, we're only interested in displaying the product name along with the phone number and contact name of each product's supplier. Finally, we'd like to sort our final results alphabetically by product name.
Before we begin, please open your Northwind sample database and ensure that you completed the steps in the Creating a Simple Query in Microsoft Access tutorial. We're going to modify that query using the Design View to produce our desired results.
I. Open Query in Design View
Last week, we used Microsoft's query wizard to create a simple query. If you recall the creation process, the query wizard did not provide us with any of the advanced options (adding criteria, hiding fields, etc.) that we need to perform this week's query. Therefore, we're going to used the more advanced Design View to make these modifications. Our first step is to invoke the Design View for our query.
1. Select the appropriate query. From the Northwind database menu, single click on the query you wish to modify. In our case, choose the "Product Supplier Listing" query that we designed last week.
2. Click the Design View icon. This icon appears in the upper left portion of the window. Immediately upon clicking this icon, you'll be presented with the Design View.
II. Adding Fields
Adding a field is one of the most common query modifications. This is usually done to either display additional information in the query results or add criteria to the query from information not displayed in the query results. In our example, the purchasing department wanted the contact name of each product's supplier displayed. As this was not one of the fields in the original query, we must add it now.
1. Chose an open table entry. Look for an entry in the field row that does not contain any information. Depending upon the size of your window you may need to use the horizontal scroll bar at the bottom of the table to locate an open entry.
2. Select the desired field. Single click in the field portion of the chosen entry and a small black down arrow will appear. Click this once and you'll be presented with a list of currently available fields. Select the field of interest by single clicking on it. In our example, we want to choose the ContactName field from the Suppliers table (listed as Suppliers.ContactName).