Question

  • Creator
    Topic
  • #2150505

    Access – Merging Tables

    Locked

    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.

All Answers

  • Author
    Replies
    • #2926190

      Clarifications

      by bkwrm79 ·

      In reply to Access – Merging Tables

      Clarifications

    • #2926166

      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.

    • #2926153

      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.

    • #2917137

      thanks!

      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.

Viewing 3 reply threads