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 question you might want to ask. 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

Note

graphics/nman.gif

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.

Caution

graphics/cman.gif

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 Catalogs in the Database window and then choose Query from the Insert menu. 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 . (Access will enclose the string in quotation marks for you.)

  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 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.

    graphics/12fig15.gif

  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. 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.

    graphics/12fig16.gif

  10. Close the query and delete the copy you created in step 1 if you want. You can save the query as UpdateCountry if you like, but you don't need to as we won't use it again.

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 be affected.

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

graphics/12fig17.gif

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 . Enter a name for the copy in the Paste Table As dialog box and click OK to make a copy of the Plantings table for safekeeping.

  2. Select Plantings in the Database window, choose Query from the Insert menu, 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 column's Criteria cell, as shown in Figure 12.18.

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

    graphics/12fig18.gif

  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.

    graphics/12fig19.gif

  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. As you can see, the Future Planting checkbox for the first record is now checked. A checked box equals True or -1. That's why you entered the value -1 in step 6.

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

    graphics/12fig20.gif

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

  10. Close the update query. Save it as UpdatePlantings if you want to keep it, but you don't need to as we won't use it again.

Note

graphics/nman.gif

To make a copy of a table, select that table in the Database window and then choose Copy from the Edit menu. Next, select Paste from the Edit menu, then enter a name for the copy in the Paste Table As dialog box, and click OK.


Caution

graphics/cman.gif

To open a select query, you can double-click it in the Database window. The same is true with an action query such as an update or delete query. However, Access doesn't just open the action query, it runs it. Be careful not to double-click these queries and run them by accident .


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. Select PlantsCopy in the Database window and then select Query from the Insert menu. 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. Add the value 3 to limit the delete task to just the medicinal records.

    graphics/12fig21.gif

  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 Yes to continue the delete task. Open the PlantsCopy table to see that you have only decorative plants left, as shown in Figure 12.22.

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

    graphics/12fig22.gif

  6. You can delete the PlantsCopy table when you're done. Also, close the delete query. You can save it as DeleteTypes if you like, but you don't need to as we won't use it again.

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. 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.

    graphics/12fig23.gif

  5. Run the query, and Access displays a dialog box asking you to enter a country. Enter United States , 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. (If you didn't change the USA entries to United States in the earlier update example, be sure to enter USA instead of United States.) Save the query as ParameterCountry if you want to keep it.

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

    graphics/12fig24.gif

    Figure 12.25. The query returns only United States records.

    graphics/12fig25.gif

Caution

graphics/cman.gif

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.


Note

graphics/nman.gif

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.


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.

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.

    graphics/12fig26.gif

  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.

    graphics/12fig27.gif

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 nulls and 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

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 both the CommonName and TypeID fields and not just the TypeID field, as before. Be careful when designing your totals views to include only the fields that pertain to the group.

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

graphics/12fig28.gif

Caution

graphics/cman.gif

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.


The Absolute Minimum

Queries are one of the most powerful tools you'll use, and fortunately, Access provides a number of query wizards and query types to take care of most situations. In this chapter, you learned how to solve some complicated problems using queries, including

  • How to find duplicates and unmatched records and how to summarize data by categories using wizards

  • How to modify and delete groups of records using action queries

  • How to interact with your query by adding parameters

  • How to summarize groups of data using aggregate functions



Absolute Beginner's Guide to Microsoft Office Access 2003
Absolute Beginners Guide to Microsoft Office Access 2003
ISBN: 0789729407
EAN: 2147483647
Year: 2002
Pages: 124

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net