Microsoft Access uses different kinds of queries to manipulate data stored in its database table in a number of ways. Sometimes you need to add, change or delete data in one or more than one existing records in the table. For all these jobs, a powerful tool in MS Access is an Update Query. You can use this tool to make global changes in one record or in a cluster of records at one go. You can think of this tool in MS Access as an advanced form of Find and Replace tool.
There are certain restriction on the types of the data you can change using Update Query. You cannot just change any data in your table using this query. You cannot update data that are in the Calculated Field as these fields do not have any permanent storage location in the computer memory. They remain only in temporary memory after Access makes the calculation .The values in the fields generated by totals or a crosstab query also can not be changed as they are calculated fields .The values in the Auto Number fields can not be updated as they are change only when a new record is added to the table. The field that contains the Primary key can not be updated. Another value that can not be changed is the value that remains in fields resulted from unique-values queries and unique-records queries.
Let’s see the step by step instructions to create an Update Query in MS Access.
Step 1: Open the database which contains the records to be updated.
Step 2: Click on Query Design in Other menu on the Design tab .This will open the query designer as well as the Show Table dialog box.
Step 3: In the Show Table dialog box, click on the Tables tab. A list of the tables present in the database will appear. Choose the table that contains the records to be updated. Click on the Add button then Close the box. The table or tables will open in one or more than one windows with the complete list of fields in them.
Step 4: you can further limit your query results if you choose. For this, you have to enter your criteria in the Criteria row.
Step 5: Click run in the Results menu on the Design tab.
Step 6: See if the records returned by the query are the same as you want to update.
Step 7: If you want to remove any field that should not be in the result, select the field and press Delete. If you want to include any additional field in the query results, drag them to the query design grid.
Step 8: On the Design tab, click on query menu and select Update. Locate the fields where the data need to be changed, and drag them to the grid. If you need, change the criteria in the Update To Row for that field.
Step 9: Click run in the Results menu on Design tab to update the records.
Access will display the updated records.