Copying data between tables

[ChrisMadore]

I'm currently working on establishing a unified database that has to house the contents of other databases and Excel sheets, and my previous attempts and copying and pasting records haven't been successful as it requires that the headers be named similarly or otherwise it runs by order, popping up with a slew of errors.

Can I tell Access where I want certain fields from one table to be copied into another? It's a contacts database all done in one table so I use last name as a primary key.

 

[lespaul]

I would first suggest that you NOT use the last name as the PK. You never know when you are going to have to add a second person with a last name of Smith or Jones.

So, you have created a table that will be the master table for collecting all these contacts from different sources and you want to import all the records from the other tables and spreadsheets into this master table?

Is that what you want to accomplish?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[ChrisMadore]

Now that I warm the old brain up and invest a little thought into this, you are indeed correct about the primary key being something that you can't have duplicates of; I'll switch to an autonumbered value.

Yes, what you described is exactly what I'm going for.

 

[ChrisMadore]

So, can this be accomplished easily and without much fuss? It seems like something should be able to do.

 

[lespaul]

Is this going to be a one time thing? Once you get all the information in the master table, everything will be kept there?

If so, I would import each table into a new database that has your master table and then write the queries needed to transfer that data to the new one:

INSERT INTO MasterTable (SELECT Field1, Field3, Field4, etc. FROM ImportedTable)

If this is going to be an on going process I would create a process that automated it.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design