Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101
Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries> Update Query
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Queries: Update Query

Button: UpdateAn update query selects certain fields in existing records and changes values in them. Why would you want to do such an update?

  • A place changes its name, like the country Ceylon changed its name to Sri Lanka. An update query can update all previous records with the new info.
  • A business changes its address and you have contact info for several people employed at that business. All those records can be found and updated at once with an update query.
  • A zip code or area code gets split into two or more areas. You may be able to create an update query to update records that got the new numbers.
  • A school adopts a new grading scale. An update query can update the letter grades so that old grades and new ones can be compared fairly.
  • You put various categories of products on sale. An update query can update the prices for all items in those categories in the online catalog.

A well-designed database stores 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. Real life databases run into odd and complex issues.

TipCascading updates: When you change the primary key for a record and cascading updates are allowed, all instances of that value in all related tables are automatically updated. This is usually a very good thing!

TipAutoNumber primary keys: Access will not change primary keys in an AutoNumber field.

WarningDanger with some update queries: If you run an update query a second time, calculated values may change yet again! Is that what you want to happen? The step-by-step below describes this type of query.

Do you remember these warnings now?

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

TipIcon: Back Up diskBackup first: Before running an action query, be sure you have a current backup of the database or an extra copy of ALL tables that will change.

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

Example: Update Query

Query Design View: Update QueryIn the Language Arts Classes database there is a set of update queries that assign a letter grade to scores. This was necessary in earlier versions of Access which did not have the Calculated data type for a field in a table. Let's look at the query Assign A to >=94.

Problem: How to calculate a letter grade based on a number grade?

A Simple Solution: Create a set of update queries, one for each letter grade.
A query that would update any number grade to the correct letter grade is a much more complex solution! It would take a whole set of 'nested IIF expressions.

Steps in the Process:

  • Create a Select query based on the table you want to update and include only fields that will be updated or that help you select the records to update.

  • Set criteria that select the records.

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

  • Enter the new value in the appropriate field.

  • View the datasheet.

  • Run the query.
    You will get a warning about how many records will be updated. The updated table does NOT open automatically.
  • Open the updated table and verify that new values appear.

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 the 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!

Of course if you change the original value for Score, the letter grade does NOT automatically update with this method. This is a serious disadvantage!

The example update query won't help either because it requires the Letter Grade field to be null! So it is still possible to have an incorrect value in the table. This kind of synchronization error drives database designers crazy. They spend many hours trying to make it impossible, or at least difficult, to make this kind of error. Since I was writing the database for my own use only, I just had to remember how things worked and work around glitches like this. Not a good plan if anyone else had been using the database!

A field with the new Calculated data type would automatically update when the original score is changed. Much, much, much better!

Icon: Experiment Experiment: Update Query
In the Language Arts Classes Icon: On Site database, enter some scores in the Scores table for A/T ID 90, the partial records that were added the example Append query. Run the various update queries to assign letter grades to those scores.

For even more practice, create a new assignment in the Assignments/Tests table, append records, add number scores, and use the update queries to get the letter grades for the new scores.

Icon: Step-by-Step 

Step-by-Step: Update Query

  Icon: Step-by-Step
What you will learn: to create a Select query that uses OR in criteria
to change the query to an update query and run it
to verify that the updates were done

Start with: Class disk, Projects database from previous lesson open.

An update query is useful when a value needs to be changed and it appears in several records or several tables. 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 or null!) 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 that uses OR Criteria

  1. Query Design View: Criteria - Cost = 0 and Budget>2000Icon: Design View Create a new query based on the Projects table.

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

  3. Add criteria to find projects with large budgets that have not finished yet:

    Cost:       0
    Budget:   >2000

  4. Icon: Datasheet View View the query's datasheet.
    No results! But there should be two, #6 BMA Medical Conference and #8 Celebrate Argentina. These two have budgets over $2000 and no cost entered yet.
    What happened?

    The blank cells for cost for those records are NULL, not with a value assigned of zero. It is a subtle difference that Access 2007, 2010, 2013, and 2016 seem to handle a bit differently than previous versions.

    Icon: Trouble Problem: Query did find some results
    You did not delete the default values of 0 when the records were created.
    Solution: Open the Projects table and delete the $0 default values for these records.

  5. Icon: Design View Switch back to Query Design View.

  6. Query Design View: Criteria - Cost = 0 or is Null and Budget>2000Edit the criteria row for the Cost columns:

    Cost: 0 or is null.

    That covers both situations!

    Notice that your typing is reformatted to Is Null. How kind of Access!

  7. Query Datasheet View: Budget>2000 (Access 2016)Icon: Datasheet View View the query's datasheet.
    Success - two records!
     

Change to Update Query

  1. Icon: Design View Switch back to Query Design View.

  2. Click the button Update Query Button: Update Query (Access 2010) on the ribbon tab Query Tools: Design.
    A new row appears in the design grid called Update To:
     

  3. Query Design View: Update the Budget value to [Budget]+500In the Update To row, in the Budget column, type:

     [Budget]+500

    This makes Access calculate the new value by adding 500 to the old value.

    TipSquare brackets: [  ] surround a field name in expressions.

  4. View query datasheet - only the updated column shows but the values have not been updated yet (Access 2010)

    Icon: Datasheet View View the datasheet again.
    Only one column shows, the one to update. But the values did not change!

    This is unexpected. You will not see the new values until you actually run the query.

  5. Icon: Design View Switch back to Query Design View.

  6. Message: You are about to update2 row(s).Icon: Run Run the query.
    A message appears to tell you that you are about to update so many rows.

  7. Click on Yes.
    You are back in the query's design grid. Did anything change? You cannot tell from here! You must go look at the Projects table (see next section).

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

    Table Datasheet View: Budget values updated (Access 2013)

  2. Button: Save (Access 2010) Save the query as QUpdate Budget.

  3. Close the query and the table.

  4. Project database Navigation Pane with action queriesView the list of queries in the Navigation Pane.
     
    Icon: QuestionCan 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? For naming action queries?