Questions

Access - Merging Tables

+
0 Votes
Locked

Access - Merging Tables

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.
  • +
    0 Votes
    Router boy

    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.

    +
    0 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    bkwrm79

    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.

  • +
    0 Votes
    Router boy

    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.

    +
    0 Votes
    Tony Hopkinson

    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.

    +
    0 Votes
    bkwrm79

    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.