Show All

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 that contains the table you want to update and the table whose values you want to copy.

   How?

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

      How?

      1. In the Database window, click Queries 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, and if you want, specify criteria and a sort order.
      6. To view the query's results, click View on the toolbar.
   2. In query Design view, click the arrow next to Query Type on the toolbar, and then click Update Query.
   3. Drag from the field list to the query design grid the fields you want to update or you want to specify criteria for.
   4. In the Criteria cell, specify the criteria if necessary.
   5. In the Update To cell for the fields you want to update, type the expression or value you want to use to change the fields, as shown in the following illustration.

      

   6. To see a list of the records that will be updated, click View on the toolbar. This list won't show the new values. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.
   7. Click Run on the toolbar to update the records.

2. If the tables aren't already joined, join them on the fields that have related information.

   How?

   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.

   Join different tables and queries

   • In query Design view, drag a field from the field list for one table or query to the equivalent field (a field of the same or compatible data type containing similar data) in the field list for the other table or query.

     

     With this type of join, Microsoft Access selects records from both tables or queries only when the values in the joined fields are equal.

   Note  Join numeric fields only if the FieldSize property settings for both are Byte, Integer, or Long Integer.

   In some cases, you want to join two copies of the same table or query, called a self-join, 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.

   Join two copies of the same table or query

   1. In query Design view, add the table to the query twice.
   2. Create the join by dragging a field in the first table's field list 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.

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

      How?

      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.
      2. Click anywhere in the second copy of the field list for the table or query, and then click Properties on the toolbar to display the property sheet.
      3. In the Alias property box, type a new name for the table or query.

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

   4. Add the LastName, FirstName, and Title fields from the first field list to the design grid.

   5. 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, which might be called ProductID.

3. In the Update To cell for the fields you want to update, type an expression 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.