Working in Query Design View

Wizards are a great way to get a lot of work done quickly, but there isn't a wizard for every query you might want to use. In Chapter 7, you learned how to create Select queries in the Query Design View window. Specifically, you learned how to base a query on more than one table and how to limit the results by displaying only the fields you need. In this section, you'll work exclusively in design view to work with some other query possibilities, including

  • Using action queries to modify and delete data

  • Using parameters to create more efficient and interactive queries

  • Displaying summary values in a Totals view



Access also supports an append query (which adds records to an existing table) and a make table query (which makes a brand-new table). Both are considered action queries, but we won't review them in this book.

About Action Queries

An action query is so named because it actually changes the data in some way. We'll show you two types of action queries in this chapter:

  • An update query modifies existing data based on some condition. For instance, you might reduce the price of all products under $20 by 15% for a limited sale event.

  • A delete query removes records based on some condition. For instance, you might delete discontinued items instead of just flagging them as discontinued.



Before executing any action query, take two steps to protect your data. First, make a copy of the table that will be changed. If the query doesn't return the appropriate results, you still have a copy of the unchanged data. Second, run the action query as a select query. This lets you see which records will be changed without actually making the change.

Modifying Data Using an Update Query

Data entry tasks often include modifying existing data. You search for the appropriate record and then update one or more fields for that record. Sometimes updates aren't confined to just one record. When this is the case, an update query is a more efficient solution because you can modify a large number of records with one simple query.

Suppose you want to change all the USA entries in the Country field in the Catalogs table to United States . With so few records, you could easily update them one at a time, but let's use an update query to illustrate how easily and quickly you could change a large number of records. Do the following:

  1. Make a copy of the Catalogs table.

  2. Select Query from the Insert menu, select Catalogs from the drop-down control, and then double-click Design View in the New Query dialog box.

  3. In Design view, add Country to the grid.

  4. Select Update from the Query menu, which adds the Update To row to the grid.

  5. You want to limit the update to only those fields that contain the string USA . To do so, enter USA in the Country column's Criteria cell .

  6. Because you want to change these USA entries to United States, enter United States in the Country column's Update To cell. At this point, your query should resemble the one shown in Figure 12.15. (We won't run the select query first to avoid confusion during this first attempt, but we will add this step in the next example.)

    Figure 12.15. The update query specifies which records to change and how to change them.


  7. Execute the query by clicking the Run button on the Query Design toolbar.

  8. Unlike a select query, the update query displays a confirmation message: You are about to update 4 row(s). Once you click Yes, you can't use the Undo command to reverse the changes. Are you sure you want to update these records? Click Yes to continue. (You could click No to abandon the query if necessary.)

  9. Delete the copy you created in step 1 if you want.

Now, open the Catalogs table so you can see for yourself that the query really did change all those records with just a few clicks. Figure 12.16 shows the modified Catalogs table.

Figure 12.16. The query changed all the USA entries to United States.


The previous example used the same field to determine the condition by which a field was changed and just how that field was changed. You can use more than one field in an update query. The next example illustrates this by updating the Future entries in the Plantings table (that you created in the last chapter) to Yes (or checked) for any record where the corresponding number of plants planted is five or less.

As you can see in Figure 12.17, only one record meets that condition. You could easily update this manually, but an update query would be more efficient in a table with numerous records that might also be affected.

Figure 12.17. The Plantings table has only one record that meets the update condition of five plants or less.


To update the Plantings table, perform the following steps:

  1. In the Database window, right-click the Plantings table and select Copy . Then right-click in the Database window and select Paste . This makes a copy of the Plantings table for safekeeping.

  2. Select Query from the Insert menu, select Plantings from the drop-down control, and double-click Design View in the New Query dialog box.

  3. Add the NumberPlanted and Future fields, and add the criteria expression <=5 to the NumberPlanted field, as shown in Figure 12.18.

    Figure 12.18. These two fields determine which fields are modified and how they are changed.


  4. Run a select query to see whether the criteria chooses the right records. It should choose just one recordthe one for "Under the front window" because it's the only record with the value 5 or less in the NumberPlanted field. You therefore know the criteria expression is correct. The query also returns the value 1 because that's the default value for any new records, but the query doesn't actually make any changes to that record because it doesn't exist yet.

  5. Return to design view and select Update Query from the Query menu.

  6. Then, add the expression 1 to the Future column's Update to cell, as shown in Figure 12.19.

    Figure 12.19. Add the update expression before running the update query.


  7. Run the query, and click Yes to confirm the query.

  8. Open the Plantings table, shown in Figure 12.20, to see the results.

    Figure 12.20. The Future Planting field in the first record is now checked.


  9. Delete the copy you made in step 1 if you want.

Deleting Data Using a Delete Query

Deleting records in bulk is just as easy as modifying recordssimply run a delete query. The premise is almost identical to the update query except that you delete records instead of modifying existing data.

To illustrate this action query, let's delete all the medicinal plants from the Plants table. To do so, follow these steps:

  1. Make a copy of the Plants table and name it PlantsCopy . This time, work with the copy because you really don't want to keep these changes.

  2. From the Insert menu, select Query , select PlantsCopy from the drop-down control, and then double-click Design View in the New Query dialog box.

  3. Add TypeID to the grid; then enter the number 3 in the Criteria cell, as shown in Figure 12.21. Run a select query to view the results, which should return only three records. Also, each field should contain the string Medicinal . Remember, the TypeID field in the Plants table is a lookup field so it displays that string but stores the value 3. (Review Chapter 6 for information on lookup fields.)

    Figure 12.21. Adding the value 3 to limit the delete task to just the medicinal records.


  4. Return to Design view and select Delete from the Query menu.

  5. Run the query, and Access will warn you that you're about to delete three records. Click OK to continue the delete task. Open the PlantsCopy table to see that you have only decorative plants left, as shown in Figure 12.22. You can delete the PlantsCopy table when you're done.

    Figure 12.22. The delete query has removed all the medicinal plants from the PlantsCopy table.


Interact with Your Query by Adding a Parameter

Until now, queries have just done what they're supposed to do. After you add a parameter (a type of expression), the query stops and asks for more information before actually executing the query. That way, you can change the results using the same query.

A parameter query isn't really a type of query in the same sense as a select or action query (which we'll review in the next section). A parameter simply lets you interact with the query when you execute it.

Now, suppose you want to view plants, but you often need to limit which plants the query returns by the country from which you purchased the plants. You can create a simple parameter query that prompts you for a country and then returns only those records that match your response. To do so, follow these steps:

  1. Select the Plants table in the Database window, and then select Query from the Insert menu. Next, double-click Design View in the New Query dialog box.

  2. In design view, add the Catalogs table to the grid by clicking the Show Table button on the Query Design toolbar, double-clicking Catalogs , and then clicking Close .

  3. Add all the fields in the Plants table to the grid. Then, add the Country field from the Catalogs table.

  4. Add the parameter expression [Please enter a country] to the Country field's Criteria cell, as shown in Figure 12.23. Be sure to include the square brackets when you type the expression.

    Figure 12.23. Enter a parameter expression in the Country's Criteria cell.


  5. Run the query, and Access displays a dialog box asking you to enter a country. Enter USA , as we've done in Figure 12.24. Then, click OK . The results are shown in Figure 12.25. As you can see, the query returned only those plant records you purchased from USA catalogs.

    Figure 12.24. Access displays a parameter prompt asking you for more information.


    Figure 12.25. The query returns only USA records.


Summarizing Results Using a Totals View

A totals query provides a special view of your dataone in which you summarize the data. Remember how the earlier crosstab query summarized your data by categories? Think of a totals view as a simplified version of a crosstab query because it summarizes the data but doesn't categorize it.



Access can handle more than one parameter expression in the same query. For instance, you could add a second prompt that returns only medicinal plants purchased from United States catalogs by adding the parameter expression [Enter a TypeID value] to the TypeID field's Criteria cell.

A totals query actually groups the data so you can analyze the group in some way, usually by performing some type of calculation on each group . For instance, you could use a totals query to tell you how many plants of each type you have. Here's how to do so:

  1. Select the Plants table in the Database window, select Query from the Insert menu, and then double-click Design View in the New Query dialog box.

  2. Add the TypeID field to the grid twice (yes, twice). Don't try to add more fields to the grid than you actually need because doing so will change the nature of the group(s).

  3. Select Totals from the View menu to add a Totals row to the grid. Both Totals cells will display the Group By aggregate function by default. Change the second to Count by selecting that function from the cell's drop-down list, shown in Figure 12.26.

    Figure 12.26. Select the Count aggregate function from the Totals drop-down list.


  4. Run the query to display the summarized results shown in Figure 12.27. Currently, you have five decorative plants and three medicinal plants.

    Figure 12.27. The query counts the number of records in each category.


An aggregate function acts on a set of records. (Earlier, you chose the Count aggregate.) Twelve options are available in that totals list, and nine of those twelve are aggregate functions:

  • Sum Totals the value in each group

  • Avg Returns the average value in a group

  • Min Returns the lowest value in a group

  • Max Returns the highest value in a group

  • Count Returns the number of items in each group (excluding blanks)

  • StDev Returns the standard deviation for each group

  • Var Returns the variance for each group

  • First Returns the first value in each group

  • Last Returns the last value in each group



You can ignore a parameter prompt, but there's not much point to that. You might expect the query to return all the records because you're not limiting the records in any way, but that's not how Access interprets the empty prompt. If you leave the prompt empty, Access most likely won't return any records. Access thinks you want to see those records that are null (empty), and because none of the Country fields are blank, the query fails to match any records and returns nothing. (Refer to p. 221.)

The other three totals options are as follows :

  • Group By Defines the group by reducing the records to unique values

  • Expression Evaluates a calculation based on an aggregate function

  • Where Specifies a condition that limits the values in each group

In step 2 of the previous exercise, we warned you about adding fields to a totals view that aren't part of the group. Doing so changes the nature of the group and consequently returns a different set of records. For instance, suppose you want to view the name of each plant along with the total count of each type in the previous exercise. Can you see the problem without viewing the actual query? We'll explain it and then show you.

The previous query returns a count by types. You can't attach a single plant to each type or group. If you try, you end up with several groupsone for each plant. For example, if you add the CommonName field to the grid, you'll get the results shown in Figure 12.28. See how the result of the Count function is now 1 for each record? That's because each group is based on the CommonName field and not the TypeID field. Be careful when designing your totals views.

Figure 12.28. The query counts each plant as a group.




Sometimes a query that groups records returns the error Cannot group on fields selected with "*" . That can happen if you use the asterisk (*) to specify all the fields in your query, which you can't do in a totals query. If that isn't the problem, check the query's Output All Fields property. If it's set to Yes, change it to No. A totals view can display only those fields considered by the aggregate functions selected in the totals cells.

Absolute Beginner's Guide to MicrosoftR Access 2002
Absolute Beginner's Guide to MicrosoftR Access 2002
Year: 2002
Pages: 133 © 2008-2017.
If you may any questions please contact us: