Access 2003 Table merging problem

ThrAx

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!

Hope someone can help!

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.