How do I copy an entire column of data from one table to another

[Guest]

I want to create a new table and have it linked to another table. I want to
move all the data from the primare key to the new table so that I can link
them. How do I copy the data to the new table (column)? Is there a better way
to link these tables? Don't they have to have one column that is the same?

Thx,
Joey

 

[Keith]

I want to create a new table and have it linked to another table. I want to
move all the data from the primare key to the new table so that I can link
them. How do I copy the data to the new table (column)? Is there a better
way
to link these tables? Don't they have to have one column that is the same?

You can copy the PK column to a new table using a Make Table query but the
relationship would be a one-to-one. Is that what you wanted?

Regards,
Keith.
www.keithwilby.com

 

[gls858]

I want to create a new table and have it linked to another table. I want to
move all the data from the primare key to the new table so that I can link
them. How do I copy the data to the new table (column)? Is there a better way
to link these tables? Don't they have to have one column that is the same?

Thx,
Joey

Typically you would not want duplicate info in two tables. It doesn't
follow the rules of normalization. What are yu trying to accomplish?
There may be a better method.

gls858

 

[Guest]

Keith,
THis is what I read in the help section. IT seems to answer my question.
However, when I made the query, nothing happens. The query seems to be in the
right format, it runs OK, I got rid of the error messeges, but my 'updated'
table still hoas only one number in the column that I wanted to populate.
There should be 1500 number moved to the new table. The new table has about
10 additional columns which are all yes/no columns, so I would expect to see
1500 records with blank yes/no columns all the way accross the row. Instead,
the new table has only one record. I could add 1500 new records one at a
time, but I don't want to have to do that. Thank you for your help.
Joey

 

[Guest]

I have a master table of names, each has a number. Each name has an
expertise. I want to list the types of experts that I need, and then indicate
if this name has that expertise. I have tried to do this by creating a talble
of the types of expertise I need, they each form a column. I was planning to
put the number of the name in a column and then link this to the master
table. That way, I can search and see who has an expertese that I am looking
for. I have about 30 types of expertise that I need. Each name can have
multiple expertises, so a column just called expertise seemed inefficient,
since it could have one or several answers. DOes this make sense?

 

[gls858]

I have a master table of names, each has a number. Each name has an
expertise. I want to list the types of experts that I need, and then indicate
if this name has that expertise. I have tried to do this by creating a talble
of the types of expertise I need, they each form a column. I was planning to
put the number of the name in a column and then link this to the master
table. That way, I can search and see who has an expertese that I am looking
for. I have about 30 types of expertise that I need. Each name can have
multiple expertises, so a column just called expertise seemed inefficient,
since it could have one or several answers. DOes this make sense?

:

What you have is a one to many relationship between name and expertise.
You should be able to have a table of names with a primary key then
another table with that primary key and the expertise field. Now when
you select a name you could have a drop down with the different skills
to select the appropriate skill/skills. In doing so you will create
entries in the tblExpertise. Joe Smith might have 5 entries and Sam Jones
might have just two. You can then design your query using these two tables
pulling name from one and expertise from the other. I think thats what
you're wanting to do right?

gls858

 

[Guest]

gls,
I think I understand, and agree that I need a one to many relationship. What
I don't understand is how I can populate the table with expertise with all of
my expertise variables if there is only room for one column of expertise next
to the column of the primary key. I was making each expertise into its own
column with a yes/no response in relation to the primary key row so that I
had a row with a primary key followed my many instances of yes or know
depending on that persons (primary key) expertise. How can there be five
instances of expertise with one primary key if the experise is only one
column? I guess I could put whatever expertise I wanted into the expertise
column next to the primary key, but then where would I put the next one for
that primary key? Do I make multiple columns of Expertise 1, 2, etc?

Also, that does not solve my problem of how to populate my table with names
and primary keys from an existing table without having to re-type them all. I
have 1500 names and thus 1500 primary keys. Should I create the new table as
a query? I tried the 'update as' query , but for some reason that did not
work. Thank you very much, I will explore Acess help files for how to create
a one to many relationship.
Joey

 

[John Vinson]

I think I understand, and agree that I need a one to many relationship. What
I don't understand is how I can populate the table with expertise with all of
my expertise variables if there is only room for one column of expertise next
to the column of the primary key.

That's not the right place for it.

You need THREE tables, not one.

If a Person can have many Expertises, and many people can have the
same Expertise, then you have a Many to Many relationship. Such a
relationship always requires a third table to model the relationship
itself:

People
PersonID
<bio information but nothing about expertise>

Expertises
ExpertiseID
Description

PersonExpertises
PersonID <link to People>
ExpertiseID <link to Expertises>
<possibly other fields such as level of skill>

If Joe Blow is an expert at three subjects, there would be three
records in PersonExpertises for him.

To display and edit this information, use a Form based on People, with
a Subform based on PersonExpertises; on this subform use a Combo Box
based on Expertises.

John W. Vinson[MVP]

 

[Guest]

Thanks Johh,
I think I get it. That solved a problem that I did not know I had. When I
first posted here it was to find out how to populate a new table with data
from another table (column to column). I was surprised that I could not cut
and paste the way I could in excel. But, your post showed me how to set up
the tables. It's so simple once I see it....three records for one person! For
the other issue. I might try saving my table as an Excel file, then save it
again as a new table.

thanks,
joey

 

[gls858]

That's not the right place for it.

You need THREE tables, not one.

If a Person can have many Expertises, and many people can have the
same Expertise, then you have a Many to Many relationship. Such a
relationship always requires a third table to model the relationship
itself:

People
PersonID
<bio information but nothing about expertise>

Expertises
ExpertiseID
Description

PersonExpertises
PersonID <link to People>
ExpertiseID <link to Expertises>
<possibly other fields such as level of skill>

If Joe Blow is an expert at three subjects, there would be three
records in PersonExpertises for him.

To display and edit this information, use a Form based on People, with
a Subform based on PersonExpertises; on this subform use a Combo Box
based on Expertises.

John W. Vinson[MVP]

Thanks for stepping in John and setting things right. I was obviously
setting him off in the wrong direction.

Sorry about that Joey.

gls858

 

[Guest]

GLS, please no need to apologize, thank you so much for your help. I will be
in my office on Tuesday to try again.
Joey