How to?: Paste contents of one cell to multiple cells in datasheet

[Guest]

Hello,

I am hoping that there is a way to copy and paste data from 1 cell to
multiple cells in datasheet view without the need to click in each "paste to"
cell and press CTRL-V? Can I do something like highlight all cells and press
CTRL-V once? I've tried that, but it only pastes to the first cell. There
are literally hundreds of such operations we need to do for a large database.
Many thanks

 

[Jeff Boyce]

Justin

It sounds like you are trying to do spreadsheetly things in an Access
relational database table. They only look the same -- they bear no
relationship to each other.

Have you looked into using an update query to update a field in a large
number of rows/records?

Are you saying that you have a table that has the same value in a particular
field in EVERY row? If so, why bother storing it at all?

If you provide a example from your data/scenario, the 'group may be able to
offer more specific assistance.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

I figured at least one person would make the spreadsheet comment. Fair
enough. The records are part of a database. It is government classified so I
cannot copy and display any records. The table is one of many numerous ones
and the wrapper is actually an ESRI Geodatabase set up.

We are not using update queries because the repeated data sporadically spans
several records. We actually have to manually review all 100,000 plus records
for these because they were incorrectly entered originally.

No, I am not say that there is the same piece of information in one field
for all records. Where did you read that? I am saying that the same
piece of information is repeated for the same field across several records,
at some points in the table, across a number of records that are not
pre-determined.

I feel that you are unnecessarily complicating the question. Quite simply:

Is it possible to paste 1 piece of data across several cells in one shot?
Yes, the cells are in the same field, spanning different records.

Regards

 

[John Vinson]

Quite simply:

Is it possible to paste 1 piece of data across several cells in one shot?

Then, quite simply: No.

John W. Vinson[MVP]

 

[Guest]

Thank you... Simple question. Simple answer.

Then, quite simply: No.

John W. Vinson[MVP]

 

[Neil]

Don't know if it's the answer you're looking for but I've been able to
achieve what you seem to be describing by using Find/Replace. This would
only work if all the cells in the column you want to paste into contain the
same data, I believe.

Neil

 

[Jeff Boyce]

Justin

It wasn't my intent to complicate things. I just didn't have a clear enough
picture to offer specific suggestions.

I'm still not sure if you are trying to "copy across" ("across several
cells"), which implies multiple fields in the same row, or "same field
across several records". You mention both.

I don't understand why having the "repeated data sporadically span[ning]
several records" would prevent you from using an update query. If you build
a select query that returns only those rows that have "the repeated data",
does that give you a smaller set to work with first?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,

Yes, sometimes, it's difficult to explain everything without seeing the
actual db. I do thank you for the help though.

It's not that the query method won't "work". The problem is one of
practicality / efficiency. If you have to adjust a query 1000 times (at
least), each time, to update 4 - 5 records (cells of the same field), you can
start to see why I'm looking for the copy from one cell - paste to 2 or more
cell option!

I think this might actually best describe the problem we are experiencing.
Further, the data to be copied is different for each copy and paste.
Otherwise, for sure, I would have used Neil's method or an update query.

As I said, this whole problem could have been avoided if the data were
entered correctly to begin with. Now cleaning the data is tedious. I can't
even do it programatically, because the decision to copy and paste requires
cross referencing microfilm of the original data. Ughhhh.

I'll try to diagram the situation here, since this might be of interest for
someone else.

C1 C2 C3 C4 C5
R1 a b c d e
R2 c d b a f
R3 g h <> x y
R4 b d <> s d
....

I want to copy contents of R1C3 to R3C3 and R4C3. If I extend the list of
records, there are literally thousands of other groups like these tat I have
to copy and paste. The destination fields may or may not currently be
occupied. The number of destination cells(records) varies. The content to be
copied changes for each group of copy and pastes.

Again, thanks for your help Jeff. If with this new information you can think
of a smart way to handle this tedious problem, I would appreciate it. Right
now, our only solution: CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C,
down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow,
CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C,
down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow,
CTRL-C, down-arrow, CTRL-C, down-arrow... you get the idea.

Justin

It wasn't my intent to complicate things. I just didn't have a clear enough
picture to offer specific suggestions.

I'm still not sure if you are trying to "copy across" ("across several
cells"), which implies multiple fields in the same row, or "same field
across several records". You mention both.

I don't understand why having the "repeated data sporadically span[ning]
several records" would prevent you from using an update query. If you build
a select query that returns only those rows that have "the repeated data",
does that give you a smaller set to work with first?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I figured at least one person would make the spreadsheet comment. Fair
enough. The records are part of a database. It is government classified so
I
cannot copy and display any records. The table is one of many numerous
ones
and the wrapper is actually an ESRI Geodatabase set up.

We are not using update queries because the repeated data sporadically
spans
several records. We actually have to manually review all 100,000 plus
records
for these because they were incorrectly entered originally.

No, I am not say that there is the same piece of information in one field
for all records. Where did you read that? I am saying that the same
piece of information is repeated for the same field across several
records,
at some points in the table, across a number of records that are not
pre-determined.

I feel that you are unnecessarily complicating the question. Quite simply:

Is it possible to paste 1 piece of data across several cells in one shot?
Yes, the cells are in the same field, spanning different records.

Regards

 

[David F Cox]

Since you are obviously experienced in Excel have you considered moving the
data to be corrected to a spreadsheet and use the more appropriate pasting
options there?

Another way about it might be to write your own paster using mouseclick
events, but this is unknown territory for me.

Hi Jeff,

Yes, sometimes, it's difficult to explain everything without seeing the
actual db. I do thank you for the help though.

It's not that the query method won't "work". The problem is one of
practicality / efficiency. If you have to adjust a query 1000 times (at
least), each time, to update 4 - 5 records (cells of the same field), you
can
start to see why I'm looking for the copy from one cell - paste to 2 or
more
cell option!

I think this might actually best describe the problem we are experiencing.
Further, the data to be copied is different for each copy and paste.
Otherwise, for sure, I would have used Neil's method or an update query.

As I said, this whole problem could have been avoided if the data were
entered correctly to begin with. Now cleaning the data is tedious. I can't
even do it programatically, because the decision to copy and paste
requires
cross referencing microfilm of the original data. Ughhhh.

I'll try to diagram the situation here, since this might be of interest
for
someone else.

C1 C2 C3 C4 C5
R1 a b c d e
R2 c d b a f
R3 g h <> x y
R4 b d <> s d
...

I want to copy contents of R1C3 to R3C3 and R4C3. If I extend the list of
records, there are literally thousands of other groups like these tat I
have
to copy and paste. The destination fields may or may not currently be
occupied. The number of destination cells(records) varies. The content to
be
copied changes for each group of copy and pastes.

Again, thanks for your help Jeff. If with this new information you can
think
of a smart way to handle this tedious problem, I would appreciate it.
Right
now, our only solution: CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C,
down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow,
CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C,
down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow, CTRL-C, down-arrow,
CTRL-C, down-arrow, CTRL-C, down-arrow... you get the idea.

Justin

It wasn't my intent to complicate things. I just didn't have a clear
enough
picture to offer specific suggestions.

I'm still not sure if you are trying to "copy across" ("across several
cells"), which implies multiple fields in the same row, or "same field
across several records". You mention both.

I don't understand why having the "repeated data sporadically span[ning]
several records" would prevent you from using an update query. If you
build
a select query that returns only those rows that have "the repeated
data",
does that give you a smaller set to work with first?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I figured at least one person would make the spreadsheet comment. Fair
enough. The records are part of a database. It is government classified
so
I
cannot copy and display any records. The table is one of many numerous
ones
and the wrapper is actually an ESRI Geodatabase set up.

We are not using update queries because the repeated data sporadically
spans
several records. We actually have to manually review all 100,000 plus
records
for these because they were incorrectly entered originally.

No, I am not say that there is the same piece of information in one
field
for all records. Where did you read that? I am saying that the
same
piece of information is repeated for the same field across several
records,
at some points in the table, across a number of records that are not
pre-determined.

I feel that you are unnecessarily complicating the question. Quite
simply:

Is it possible to paste 1 piece of data across several cells in one
shot?
Yes, the cells are in the same field, spanning different records.

Regards

:

Justin

It sounds like you are trying to do spreadsheetly things in an Access
relational database table. They only look the same -- they bear no
relationship to each other.

Have you looked into using an update query to update a field in a
large
number of rows/records?

Are you saying that you have a table that has the same value in a
particular
field in EVERY row? If so, why bother storing it at all?

If you provide a example from your data/scenario, the 'group may be
able
to
offer more specific assistance.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello,

I am hoping that there is a way to copy and paste data from 1 cell
to
multiple cells in datasheet view without the need to click in each
"paste
to"
cell and press CTRL-V? Can I do something like highlight all cells
and
press
CTRL-V once? I've tried that, but it only pastes to the first cell.
There
are literally hundreds of such operations we need to do for a large
database.
Many thanks