Move columns from one table to another (ADP)

Microsoft Office Access 2003

previous page next page
  1. In the Database window, click Database Diagrams Button image under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
  2. In your database diagram, select the columns that you want to move.
  3. Click the Cut button on the toolbar. This action deletes the selection from the table and places the column and its current set of properties on the Clipboard.
  4. Position the cursor in the new table at the location where you want to insert the columns.
  5. Click the Paste button on the toolbar. The columns are inserted at the new location and remain highlighted until you click elsewhere.

ShowMove columns with data from one table to another

  1. In the Database window, click Database Diagrams Button image under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
  2. In your database diagram, select the columns that you want to move.
  3. Click the Copy button on the toolbar. This action places the selection with its current set of properties on the Clipboard.
  4. Position the cursor in the new table at the location where you want to insert the columns.
  5. Click the Paste button on the toolbar. The columns are inserted at the new location and remain highlighted until you click elsewhere.
  6. Create an update query to add the data to the table to which you copied the columns.

    ShowHow?

    Caution   You cannot undo the action of executing an Update query. As a precaution, back up your data before executing the query.

    ShowBackground information

    You can change the contents of multiple rows in one operation by using an Update query. For example, in a titles table you can use an Update query to add 10% to the price of all books for a particular publisher.

    When you create an Update query, you specify:

    • The table to update.
    • The columns whose contents you want to update.
    • The value or expression to use to update the individual columns.
    • Search conditions to define the rows you want to update.

    For example, the following query updates the titles table by adding 10% to the price of all titles for one publisher:

    UPDATE titles
SET price = price * 1.1
WHERE (pub_id = '0766')

    ShowCreate an Update query

    1. In the Database window, click Queries Button image under Objects, and then click New.
    2. In the New Query dialog box, select Design Stored Procedure.
    3. In the Add Table dialog box, add the table, view, or function you want to use to update, and then click Close.
    4. On the Query menu click Append Query.

      Note  If more than one table is displayed in the Grid pane when you start the Update query, the Query Designer displays the Update Table dialog box to prompt you for the name of the table to update.

    5. Define the data columns to update by adding them to the query. Columns will be updated only if you add them to the query.
    6. In the New Value column of the Grid pane, enter the update value for the column. You can enter literal values, column names, or expressions. The value must match (or be compatible with) the data type of the column you are updating.

      Caution   The Query Designer cannot check that a value fits within the length of the column you are updating. If you provide a value that is too long, it might be truncated without warning. For example, if a name column is 20 characters long but you specify an update value of 25 characters, the last 5 characters might be truncated.

    7. Define the rows to update by entering search conditions in the Criteria column.

      If you do not specify a search condition, all rows will be updated.

      Note   When you add a column to the Grid pane for use in a search condition, the Query Designer also adds it to the list of columns to be updated. If you want to use a column for a search condition but not update it, clear the check box next to the column name in the rectangle representing the table, view, or function . ( Icon image ).

    When you execute an Update query by using the Run Button image button on the Query Designer toolbar, a message appears indicating how many rows were changed.

  7. Run the update query.

    ShowHow?

    When you have finished designing your query, you can run it.

    1. If you want to test whether the syntax of the query you are creating is correct, you can verify the query.

      ShowHow?

      To avoid problems, you can check the query you have built to ensure its syntax is correct. This option is especially useful when you enter statements in the SQL pane.

      Note   A statement can be valid, and therefore be verified successfully, even if it cannot be represented in the Diagram and Grid panes.

      Note   SQL Verification can detect some, but not all SQL errors. If a query contains an error not detected during SQL verification, the database will detect the error when you run the query.

      1. In the Database window, click Queries Button image under Objects, click the query you want to open, and then click Design on the database window toolbar.
      2. Click Verify SQL Syntax Button image.
    2. Click Run Button image.

    Notes

    • If you are creating a Select query, the results of the query appear in datasheet view.

    • If you are creating an Update, Append, Append Values, Delete, or Make-Table query, the Query Designer displays a message indicating how many rows were affected by the query.

    • To stop a query that is taking too long to finish, press CTRL+BREAK.

  8. Return to the database diagram and delete the columns from the original table.
previous page start next page