6. MICROSOFT ACCESS QUERIES – ADVANCED
6.7.4: Update Query
The type of query used to modify existing rows in a table is the Update query. In order to create such a query, you should begin with a Simple query that retrieves the rows that are to be updated and then change the type to Update. When you change the type to Update MS Access will add a new row to the Grid area where you specify the new values for each field to be updated. The new value can be the result from a calculation.
Example
Suppose we wish to update the course Table so the credit hours are doubled for each ACS course. We begin with a Simple query to retrieve the PK field, the fields to be updated, and the fields needed for selection criteria purposes. In this case we will need a Simple query to retrieve the department code, course number, and credit hours fields:
Next, we change the query type to Update and MS Access modifies the Grid to include an UpdateTo line. On that line we enter an expression that generates the new values. To double the credit hours, we need the expression [creditHours]*2, as in:
Exercises
1) Create a table of ACS courses, but name the new table ScienceCourses.
2) Does the table ScienceCourses have a primary key? If not, create one.
3) Run a delete query on ScienceCourses to delete all non-3-credit hour courses.
4) Append all 3-credit hour MATH courses to ScienceCourses.
5) Run an update query on ScienceCourses to double the credit hours of all 3-credit hour courses.
