Copying records from one table to another

  • Thread starter Thread starter tdp
  • Start date Start date
T

tdp

Hi--I am trying to set up a database with multiple
tables. When I try to copy the data from one table to
another to avoid having to retype it by hand (I will call
the tables "table 1" and "table 2"), I am having two
problems:
1) For my primary key field, I have "mailing list id",
which is a number. When I paste append the values from
table 1 to table 2 (highlighting the values in datasheet
view of table 1, ctrl <c>,paste appending them into the
correct column of table 2), table 2 accepts the values,
but even though I'm clicking "save" before I close table
2, the values are reset to 0 when I open table 2 again.
2) For one of my other fields, called "last name",
table 2 will not accept the values from table 1,
saying "the value you entered is not valid for this
field". I double-checked the data type for "last name"
in table 2 by looking at the design view, and it is the
right data type with plenty of room for the number of
characters. Why will table 2 not accept the data?

I have noticed when viewing the Office Clipboard that the
Clipboard does not list the names in a column, but like
one long text string--is that the problem?

I'm very confused!

Thanks so much for helping an Access newbie,
tdp
 
Hi--I am trying to set up a database with multiple
tables. When I try to copy the data from one table to
another to avoid having to retype it by hand (I will call
the tables "table 1" and "table 2"), I am having two
problems:
Click to expand...

Well... consider this concept: a relational database is based on the
"Grandmother's Pantry Principle" - "A place - ONE place! - for
everything, everything in its place". You should enter data into a
single table, ONCE, and not try to store it in any other table.
Instead you would use a Query to link the two tables to pick up the
information in the other table.
1) For my primary key field, I have "mailing list id",
which is a number.
Click to expand...

Each table should have ITS OWN distinct primary key, a field which
uniquely identifies the entity represented by that table. I'm not
clear what your two tables are, and how they are related - but I
suspect the problem is in the table design.
When I paste append the values from
table 1 to table 2 (highlighting the values in datasheet
view of table 1, ctrl <c>,paste appending them into the
correct column of table 2), table 2 accepts the values,
but even though I'm clicking "save" before I close table
2, the values are reset to 0 when I open table 2 again.
Click to expand...

Copy and paste is NOT typically a particularly useful technique in
relational databases! First off, as noted above, one tries to avoid
storing the same information in two different places in the first
place; in the cases where you need to do so, it's usually better to
run an Append query or use some other technique.
2) For one of my other fields, called "last name",
table 2 will not accept the values from table 1,
saying "the value you entered is not valid for this
field". I double-checked the data type for "last name"
in table 2 by looking at the design view, and it is the
right data type with plenty of room for the number of
characters. Why will table 2 not accept the data?
Click to expand...

Possibly because one or the other of the fields is a "Lookup" field.
This is a deceptive feature (misfeature, many feel) in that it SHOWS
you a name (for example), but what is actually stored in the table is
a concealed number, a link to the primary key of the lookup table. Is
this the case for you?
I have noticed when viewing the Office Clipboard that the
Clipboard does not list the names in a column, but like
one long text string--is that the problem?
Click to expand...

I think you need to take a step back and reconsider your table design
to avoid this operation altogether. What are your two tables? How are
they related? What information do they contain?
 
Hi, John--Thanks for your reply.
I am sure the table design could be better <vbg>!!
My two tables are:"mailing list", containing names and
addresses, and "cards sent/received", containing
information about whether or not each person in "mailing
list" was sent a card or a card was received from them in
different years. I let Access number each record and
used that as the primary key in Mailing List (that field
is called "mailing list id"), as there are several
records where the names are identical but the addresses
are different.

I understand the concept of not wanting to repeat data
between tables, but you have to have at least one field
of repeat data to link the tables, don't you? I am
trying to copy the information in the common field
(which, in my case,is "mailing list id") from one table
to another to avoid having to retype it.
This is a deceptive feature (misfeature, many feel) in that it SHOWS
you a name (for example), but what is actually stored in the table is
a concealed number, a link to the primary key of the lookup table. Is
this the case for you?
Click to expand...

As far as I can tell with my absolutely beginner's Access
experience, no. Both fields are data type "text" with a
character length of 50. However, at this stage I do not
know enough about Lookup Fields to know for sure.

Thanks again,
tdp
 
Back
Top