Software

Ask something
Discussion Locked

Access Paste

0 helpful
8 replies

by maine kebo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
Does anyone know how to paste values to one column of multiple rows at once in Access97? I tried the Paste Append but it is not exactly what I want.

Any ideas are highly appreciated.

8 replies

by sl-campbell

Click on the column header so that the whole column is marked. And then paste the values. The field type must be the same as the source. The values however will be pasted in the order that they are copied with-out reference to the matching records in the source column.
To have a column updated from another table you need to create a UPDATE query with a join on the two tables.

by sl-campbell

If you are copying the values from another table/column, then you must first completely mark the source column, press Ctrl-C, then completely mark the destination column, and then press Ctrl-V.
As I said, the field types of source and destination must be the same, and the rows of the source must match up exactly with the rows of the destination column or you may have values copied to rows that you do not want.
In this case you will need to use an Update query - this is only possible if the one table has a unique field and the other table has this same field, either as a one to one, or one to many relation. If this is the case, you should see a joining line between the tables on that field. If not,(such as the case where the two fields do not have the same name), then try to create the join by click the field in question in the one table and click-drag to the matching field in the other table until a join line is shown.
Then select the field/column if the destination table that is to be updated(double-click - it should show up in the grid). Then, in the update or value line in the grid type in the name of the table and field as in:
[mySourceTable]![mySourceField]
Then execute the query (click the red exclamation)
Go to the query window and design view. Select the source table and destination tables. Then select the menu point: Query|Update query.

by maine kebo

Thanks for the input!!
I tried to highlight the whole column but I could only copy the value to one row of a column at a time. In fact, I wanted to update the value of a number of rows in a column fast, rather than going thru each row one by one.

by jmedynski

It depends on the type of information that you are trying to paste. There are several ways to do this and you are not limited to using only other Access Tables.

[u]Copying a column in Access[/u]

1) Open the Access Table that you want to copy the information from.
2)Select the entire column to be copied and then "Copy" by either right clicking or selecting "Copy" from the Edit Menu.
3) Open the Access Table where you want to insert the information.
4) Place the cursor on one of the Columns and "Paste" by either right clicking or selecting "Paste" from the Edit Menu.

This will insert the new column into this table. It will not overwrite another column, it will create a new one.

[u]Copying a column from Excel to Access[/u]

There are a few ways to modify the information. The way you do it depends on what you are trying to accomplish.

If you want to make sure that the information that you are adding is associated with the correct row of the existing column "Copy" the reference column as described above, but "Paste" it into Excel.

Excel offers a lot more user friendly options for copying similar data that Access Tables make more difficult.

Once you have all of the information added in the way you want, "Copy"the new column from Excel and "Paste" it to the Access Table.

[u]Summary[/u]

Basically it all depends on why you want to add a whole column of information to a table. If you have the information already created in either Access or Excel, then this works really well.

If you are creating a new Column in the table and just have to update it, then you may find it easier in Excel for reasons stated above, or just create a data entry form with a minimum number of fields. This helps input data without the clutter of a table.

To offer more accurate information I would need to know why you are adding the data for an entire column.

by shanghai sam

Thanks for the tips on copying and pasting between Excel & Access. I just need to copy values to a certain rows of a column in Access and I found your suggestion is very to the point. It works really well now.
Thanks again for your help!

This discussion is closed to new replies.