Tables & Queries:
Update Query

An update query selects certain fields and records and changes values in them. For example, if a customer's phone number or address changes, you can update all of the orders for that customer to the new information. If two companies merge, you might want to update the CustomerID. A well-designed database stores such data in only one location. However, primary keys can be used in other tables as foreign keys, so those values can appear in several tables, even when following good design principles.

Cascading updates: An update query that changes a primary key value can also affect other tables. If cascading updates are allowed, when you change the primary key for a record, all instances of that value in all tables are automatically updated. This is usually a very good thing!

Danger with some update queries: If you run an update query a second time, calculated values may change yet again! The step-by-step below uses this type of query.

Do you remember these warnings now?

No Undo: You cannot "undo" an action query's action.

Verify selection first: Be sure you have selected the records that you thought you did! Use the View button to see the query's datasheet. Check the records carefully.  Then run the action query with the Run button .

Example: Update Query

In the LanguageArtsClasses database there is a set of update queries that assign a letter grade to scores. Let's look at the query Assign A to >=94.

Problem: I need to convert number grades to letter grades.

Solution: Create a set of update queries, one for each letter grade.

Steps in the Process:

• Create a Select query to select the tables and fields needed to select what you want to update.

• Set criteria.

• Change the query to an Update Query. A new row appears in the grid, Update To.

• Enter the new value.

• View the datasheet.

• Run the query. You will get a warning about how many records will be updated.

The example query uses only one table, Scores, and only two fields from that table, LetterGrade and Score. The update query looks at the value in the Score field. If it meets the criteria >=94,  then the LetterGrade field is updated to A, the value in the row Update To. Similar queries have criteria for other grade ranges and assign the correct letter grade for each one.

The advantage of using update queries to assign letter grades is that they won't make a mistake! And, it avoids a lot of data entry. I really liked that part!

Experiment: In the LanguageArtsClasses database, enter some scores in the Scores table for A/T ID 88, the partial records that were added the example Append query. Run the various update queries to assign letter grades to those scores.

Step-by-Step: Update Query

---

What you will learn:

to create an update query

---

Start with:  , Projects database open.

An update query is useful when a value needs to be changed and it appears in several places. When postal code areas or telephone code areas are split, you may have many entries to update. The hard part is figuring out a way to select the records that need to be changed.

For this section, you will be updating the Budget values for projects that meet certain criteria. We want to pick out the projects that have not finished yet (Cost would be zero!) and those with large budgets. Those budgets will be increased by $500.

Again, with just a few records there is no real need to use a query. Practice, however, IS what you need!

Create Select Query with Criteria

1. Create a new query based on the Projects table.
    

2. Drag the fields ProjectID, Cost, and Budget to the design grid.
    

3. Add criteria as follows:
   Cost:        0
   Budget:    >2000
    
   These criteria should pick out the projects with large budgets that have not finished yet.
    

4. View the query's datasheet.
   Only two projects meet the criteria, record #6 and #8.
    

---

Change to Update Query

1. Switch back to Query Design View.
    

2. Click the Query Type button and select Update Query.
   The query's title bar changes and a new row appears in the design grid called Update To:
    

3. In the Update To row, under Budget, type:
    [Budget]+500.
   This makes Access calculate the new value by adding 500 to the old value.
   Square brackets [  ] surround a field name in expressions.
    

4. View the datasheet again. No changes! This is unexpected. You will not see the new values until you actually run the query.
    

5. Run the query.
   A message appears to tell you that you are about to update so many rows.
    

6. Click on Yes.
   You are back in the query's design grid. Did anything change? You must go look at the Projects table.
   Running this query again will increase the new values by another 500! There will be no warning!!!

---

Verify Updates

1. Open the table Projects in Datasheet view.
    
   The two values have been updated.
    

2. Save the query as Update Budget.
    

3. Close the query and the table.
    

4. View the list of queries in the Database Window.
    
   Can you pick out the action queries in the list without reading the names?
   Thinking to the future, which of these queries really needed to be saved? Any of them?
   Are the names clear enough? If not, what would you suggest as a method for naming queries? Naming action queries?
   

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012