Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Access 2003 Table merging problem

Options
  • 23-06-2011 11:37am
    #1
    ThrAx
    Registered Users Posts: 973 ✭✭✭


    POSTED THIS IN THE WRONG FORUM! Mod could you please move it to Development! Sorry!

    I have a load of tables in my access db. Each one has the same field names but they are arranged differently in a lot of them. :eek:

    Like this except with more fields and on nearly 150 tables

    ID | LOT_NO | X1 | X2

    and

    LOT_NO | ID | X2 | X1

    I'm trying to merge them with probably a Union query and but data is ending up in the wrong fields because they are arranged differently in each table. So, what I need is a way to arrange all the fields the same in each table using an SQL query or VBA or anything that doesn't involve going through each table and doing it manually!
    :confused:
    Hope someone can help!
    Tagged:


Comments

  • Options
    #2
    FSL
    Registered Users Posts: 1,456 ✭✭✭FSL


    If the field names are the same in each of the tables then can you not simply Insert into Newtable select field1,field2 .......fieldn from Existingtable1. where you select the fields in the order you want them to appear in the new table.

    If there are/ could be duplicate records you would need a where clause to exclude any that are already in the the merged table.

    You could do it in VBA by reading the list of tables and recursively calling the SQL statement using the table names from the list of tables.


Advertisement