Update one table based on another table (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

  1. Create an update query (update query: An action query (SQL statement) that changes a set of records according to criteria (search conditions) that you specify.) that contains the table you want to update and the table whose values you want to copy.

ShowHow?

  1. Create a query with the tables or queries that include the records you want to update.

ShowHow?

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click Queries Button image under Objects, and then click New on the Database window toolbar.
  2. In the New Query dialog box, click Design View, and then click OK.
  3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
  4. Double-click the name of each object you want to add to the query, and then click Close.
  5. Add fields to the Field row in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), and if you want, specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) and a sort order.
  6. To view the query's results, click View Button image on the toolbar.
  1. In query Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), click the arrow next to Query Type Button image on the toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), and then click Update Query.
  2. Drag from the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.) the fields you want to update or you want to specify criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) for.
  3. In the Criteria cell, specify the criteria if necessary.
  4. In the Update To cell for the fields you want to update, type the expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) or value you want to use to change the fields, as shown in the following illustration.

Enter an expression to change a group of records

  1. To see a list of the records that will be updated, click View Button image on the toolbar. This list won't show the new values. To return to query Design view, click View Button image on the toolbar again. Make any changes you want in Design view.
  2. Click Run Button image on the toolbar to update the records.
  1. If the tables aren't already joined (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.), join them on the fields that have related information.

ShowHow?

In most cases, you want to bring together or perform an action on data from more than one table or query. For example, you might want to view a customer's information with the orders the customer placed. To see this information, you need data from the Customers and Orders tables.

ShowJoin different tables and queries

In some cases, you want to join two copies of the same table or query, called a self-join (self-join: A join in which a table is joined to itself. Records from the table are combined with other records from the same table when there are matching values in the joined fields.), that combines records from the same table when there are matching values in the joined fields. For example, say you have an Employees table in which the ReportsTo field for each employee's record displays his or her manager's ID instead of name. You could use a self-join to display the manager's name in each employee's record instead.

ShowJoin two copies of the same table or query

  1. In query Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), add the table to the query twice.
  2. Create the join (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) by dragging a field in the first table's field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) to the field you want to relate it to in the second field list.

 Note   Microsoft Access appends "_1" to the table name in the second field list. For example, if you add the Employees table twice, the first field list is titled "Employees," and the second field list is titled "Employees_1." You can rename the table to something more descriptive by setting the Alias or Caption property for the field list.

ShowExample of a self-join using the Employees table

To display the managers' names in the ReportsTo field instead of the managers' IDs, follow these steps.

  1. In query Design view, add the Employees table to the query twice.
  2. Rename the second table in the query.

ShowHow?

 Note   Renaming copies of a table or query in a query doesn't rename the underlying table or query.

  1. Open a query in Design view (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.).
  2. Click anywhere in the second copy of the field list (field list: A window that lists all the fields in the underlying record source or database object, except in data access page Design view. In data access page Design view, it lists all the record sources and their fields in the underlying database.) for the table or query, and then click Properties Button image on the toolbar to display the property sheet.
  3. In the Alias property box, type a new name for the table or query.
  1. Create the join by dragging the ReportsTo field in the first table's field list to the EmployeeID field in the second table's field list.
  2. Add the LastName, FirstName, and Title fields from the first field list to the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.).
  3. Add the LastName field from the second field list to the design grid. To display the field name as "Manager" instead of "LastName," set the Caption property in this field's property sheet.

For example, if you want to copy data from the ProductName field in the Products table to a field in another table, join the two tables on the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.), which might be called ProductID.

  1. In the Update To cell for the fields you want to update, type an expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) with the following syntax:

[tablename].[fieldname]

where tablename and fieldname are the names of the table and field that contain the data you're copying.

For example, if you are copying data from the ProductName field in the Products table, you would type [Products].[ProductName] in the Update To cell.

 
 
Applies to:
Access 2003