Access - Merging Tables

By bkwrm79 ·
I'd appreciate any advice on the problem I've been struggling with the last few days.

The small place of business where I work has a database in Access 2000. At some point a separate table was created for information on clients from one of the three municipalities we serve.

I've made sure both tables have all the same fields, in the same order. I probably need to go through again to make sure length and data type match in all instances.

A bigger problem (I think) is that both have autonumber fields, both starting at 1.

What's the easiest way to merge these tables? I've seen references to Append Queries and conflicting information on Paste Appending. So far none of my experiments have been successful, but I'm not sure what I'm doing wrong, or even which approach I should be focusing on.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

The easiest solution

by Router boy In reply to Access - Merging Tables

may be to to export the data in the table out into a file an re- import it into the the existing table you were planning to merge into. When you export the data out into a file you can simply remove the column containing the unique identifier. Thus when you import into the other table it will pickup with the last number in the file.

Collapse -

Depends on whether you are using the auto number

by Tony Hopkinson In reply to Access - Merging Tables

as a link to other tables.
If not then
Insert MergeTable (Column2,Column3... Column99)
Select Column, Column2...Column99 From SeperateTable, given the field types are equivalent, (there are ways round that) should do the job.

If you are and there are conflicts (ie used the same number in each table) then you've got problems.

Lots of options but it depends on how the rest of your schema is laid out and how it's used.

Collapse -


by bkwrm79 In reply to Access - Merging Tables

Thanks to Router Boy and Tony Hopkinson.

I was able to successfully merge the tables as per Tony's suggestion, as the 2nd table (not yet normalized) wasn't linked to any other tables.

It's great to have this step completed. It gave me a lot of trouble.

Related Discussions

Related Forums