Tables & Queries:
Update Query

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

An update query Button: Update 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.

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

WarningDanger 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?

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

TipVerify selection first: Be sure you have selected the records that you thought you did! Use the View button Button: Views - datasheet (2003) to see the query's datasheet. Check the records carefully.  Then run the action query with the Run button Button: Run (2003).

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    ExercisesTo subtopics

Project 4: Forms & Reports


Example: Update Query

Query Design View: Update QueryIn 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!

Icon: Experiment 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.

Icon: Step-by-Step 

Step-by-Step: Update Query

 Icon: Step-by-Step

What you will learn:

to create an update query

Start with:  Class disk, 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. Query Design View: Criteria - Cost = 0 and Budget>2000Create 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. Query Datasheet View: Budget>2000Icon: Datasheet View the query's datasheet.
    Only two projects meet the criteria, record #6 and #8.

Change to Update Query

  1. Button: Query Type - Update QueryIcon: Design 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. Query Design View: Update the Budget value to [Budget]+500In the Update To row, under Budget, type:
    This makes Access calculate the new value by adding 500 to the old value.
    TipSquare brackets [  ] surround a field name in expressions.

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

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

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

Verify Updates

  1. Table Datasheet View: Budget values updatedOpen the table Projects in Datasheet view.
    The two values have been updated.

  2. Icon: Save Save the query as Update Budget.

  3. Close the query and the table.

  4. Projects Database Window: Queries, with action queriesView the list of queries in the Database Window.
    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? Naming action queries?