ACC1x: Copying Columns in Microsoft Access

ID: Q92692

The information in this article applies to:

Microsoft Access versions 1.0, 1.1

SUMMARY

When you are viewing or editing a table in Datasheet view, you cannot select a column of data, copy it to the Clipboard, and then edit it, as you can with Microsoft Excel. You can, however, change or rearrange data without copying it to the Clipboard. This article describes procedures for the following three methods of manipulating data in a Microsoft Access table:

Rearranging the column order

Adding a column

Copying a column of data to another application

MORE INFORMATION

Rearranging the Column Order

Use the following steps to rearrange the order of the columns in a Microsoft Access table:

Use the column indicator to select the column.

Drag the column to the new position, and then release it.

For more information, search for "Column Width (datasheets)," and then "Changing a Datasheet's Appearance" using the Microsoft Access Help menu.

Adding a Column

This section describes how to add a column of information from one Microsoft Access table to another.

The first procedure describes how to update information in Table1 with information from Table2. The second procedure describes how to create two tables (MASTER and EXTRAINFO), and then add the information from EXTRAINFO to the MASTER table.

To add a column to a Microsoft Access table, you must run an update query. If you are also adding new records, you must run an append query (refer to step 3, in either procedure) before you run the update query.

Copying a Column from One Table to Another:

Use the following steps to update information in Table1 with information from Table2:

Set the same primary key for both Table1 and Table2.

Manually add the new column names from Table2 to Table1. Make sure they are the same data type. They do not have to be the same name as the corresponding fields in Table2, but it saves a lot of typing if the names are the same.

Use Table2 to create an append query.

NOTE: Skip this step if Table2 contains only records that exist in Table1.

Choose the Query button, then choose New. Add Table2 to the query, then close the Add Table dialog box.

From the Query menu, choose Append.

In the Query Properties box, specify Table1 as the Append To Table Name. Make sure that the Unique Values check box is selected, then choose the OK button.

Add all the field names from Table2 to the QBE grid.

If the field names are spelled correctly, Microsoft Access automatically fills in the Append To field with the name of the correct field from Table1. If not, you must manually select the Table1 field name in the Append To field.

Run the query. New records from Table2 are added to Table1.

Create an update query that contains both Table1 and Table2.

Choose the Query button, then choose Update. Add both Table1 and Table2 to the query, then close the Add Table dialog box.

Join the two tables by dragging the key field from Table1 to Table2.

From the Query menu, choose Update.

Drag the new fields from Table1 and drop them into the QBE grid, then type the [Table2]![<fieldname>] into the Update From field. (You must manually type this entry.)

Run the query to update the new fields in existing Table1 records.

Creating Two Tables and Adding Information from One to Another:

Create two tables called MASTER and EXTRAINFO. The MASTER table has two fields: KEY and ITEM1. The EXTRAINFO table has two fields: KEY and ITEM2. Add data as follows:


      MASTER:  KEY   ITEM1          EXTRAINFO:  KEY   ITEM2
                 1   AAAAA                        1   11111
                 2   BBBBB                        3   33333

Open MASTER in Design view and add the field ITEM2. It should look like this:


      MASTER:  KEY   ITEM1   ITEM2
                 1   AAAAA
                 2   BBBBB

Create the append query as follows:

Choose the Query button, then choose New. Add EXTRAINFO to the query, then close the Add Table dialog box.

From the Query menu, choose Append.

In the Query Properties dialog box, select MASTER as the Append To Table Name. Select the Unique Values check box, then choose the OK button.

Drag the KEY field and the new field, ITEM2, and drop them into the QBE grid. Microsoft Access fills in the Append To field names.

From the Query menu, choose Run.

When Microsoft Access displays the following message, choose the OK button:
```
          2 rows will be appended. 
```

When Microsoft Access displays the following message, choose the OK button:
```
          1 record(s) were lost due to key violations 
```
NOTE: This is expected; the record with KEY=1 could not be appended. The update query (step 4) will copy this record.

MASTER should now look like this:
```
         MASTER:  KEY   ITEM1   ITEM2
                    1   AAAAA
                    2   BBBBB
                    3           33333 
```

Create the update query as follows:

Choose the Query button, then choose Update. Add both EXTRAINFO and MASTER to the query, then close the Add Table dialog box.

Join the tables by dragging the KEY field from EXTRAINFO to MASTER. A line will connect the two tables.

From the Query menu, choose Update.

Drag the new field, ITEM2, from MASTER and drop it into the QBE grid. Then, type [EXTRAINFO]![ITEM2] in the Update From field.

From the Query menu, choose Run.

When Microsoft Access displays the following message, choose the OK button:

2 rows will be updated.

MASTER should now look like this:


         MASTER:  KEY   ITEM1   ITEM2
                    1   AAAAA   11111
                    2   BBBBB
                    3           33333

For more information on append and update queries, search on the following topics using the Microsoft Access Help menu:


   "Appending Records to a Table"

   "Update Queries, Updating Records as a Group"

   "Joins, Joining Tables in a Query"

Copying a Column of Data to Another Application

If you need to copy a column of data from Microsoft Access to another application, such as Microsoft Excel, try one of the following methods:

Use Copy & Paste to transfer the records you want to copy. Then delete any unwanted columns in the other application.

Use the Hide Columns command from the Layout menu to suppress the columns that you do not want copied. Then use Copy & Paste to transfer the records that you want to copy. You must Unhide each column individually.

Create a query that contains only the columns that you want. Then use Copy & Paste to transfer the records that you want to copy. This method is useful if you plan to copy data on a regular basis.

For more information, search for "Copy Command" using the Microsoft Access Help menu.

REFERENCES

Microsoft Access "User's Guide," version 1.0 and 1.1, Chapter 6, "Appending Records," Chapter 7, "Updating Tables," and Chapter 14, "Changing Column Width and Order"

Keywords : kbusage GnlOthr
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo