I have 10 tables that all have the same columns with the same names.
I have created a new table that has all those same field names.
I want the rows in all 10 tables to pull into one table or query. Would prefer a table.

I would just combine all the tables but they're linked from other databases and other departments have to use those individual tables to make their forms and stuff work.

I also have another table that has the same fields but also has more fields. I would like to JUST pull in the like fields so that data can be displayed as well.

I will use letters for fields names (to make it easy)

Create a new query (Change the query type to UNION Query)

SELECT A,B,C,D,E,F,G FROM TABLE1
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE2
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE3
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE4
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE5
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE6
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE7
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE8
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE9
UNION ALL
SELECT A,B,C,D,E,F,G FROM TABLE10


Save it
Then create another query (Set it as a make table)
Use the Unionquery as the source

Make sure you use the union ALL (otherwise it will combine rows that match)

I don't show any options to set it as a make table or to use a unionquery. I'm on Access 2003 does that make a difference?

Since you have to keep the individual tables separate, I agree a Union query is the best solution. You do NOT want to duplicate data.

You might have to add a field to identify the source table unless there is something else that does that. For example:

SELECT A,B,C,D,E,F,G, "table1" As Tablename FROM TABLE1
UNION ALL
SELECT A,B,C,D,E,F,G, "table2" As Tablename FROM TABLE2

etc.

To enter a union query you have to actually type in the code in SQL mode. Open a new query in design mode then select SQL view

Switch to Queries
Select New Query > Design View
Close the show table window
In the Query Menu Select SQL Specific > Union Query

It does the same as just clicking the "view sql"

Then as Scott Said... you have to type in the sql.
If there is a risk of duplicated data, use scott's example and add a column to show which table it came from.

Took care of all that. Moved it all over to test with the guys, they said it's good. I moved it to Production a few minutes ago. Yeah!

Only bad thing that happened is when I moved my form over to read the new database some how the save and enter new part number button went pffffffft 'cause it won't work now. Trying to figure that out and I'm all good. Right now they're having to open the form again to pull a new part number hehe but they're cool with that. :)

Hopefully I'll get this sucker figured out. UGH :(

Quote:

---

Originally Posted by StaticFX

Switch to Queries
Select New Query > Design View
Close the show table window
in the Query Menu Select SQL Specific > Union Query

It does the same as just clicking the "view sql"

then as Scott Said... you have to type in the sql.
If there is a risk of duplicated data, use scott's example and add a column to show which table it came from.

---

I just went into the individual tables and put a new column in and just pasted in the name of the database it came from then set it to auto populate that same word when new ones are entered. There are no duplicates and it's working great. :)