append query

[Dsperry101 via AccessMonster.com]

Sirs,

I have a database that has several tables in it that have the same column
names.
I have realized that this should be one table. How do I do an append from the
tables
and append them to one table. I've tried several queries haven't got it yet.


Lets say the tables are tbl113 , tbl114 , tbl115 , tbl116

The fields are DPGid , Description , Dept , Post

Want the new table to be tblDepartments

Probaby pretty simple , but I'm new to access and am still learning.


Thanks

Dan
S

 

[John Spencer]

I would copy one of the existing tables and Name it tblDepartments.
Then I would append the data from the remaining tables into the new table.

INSERT INTO tblDepartments
SELECT * FROM tbl114

To build that query in the query grid, open a new query
Add the SOURCE query tbl114 to the grid
Select Query: Append query from the menu
In the dialog that appears select tblDepartments
In the grid, double click on the "*" in the tbl114
Select Query: Run from the menu

If you need to be more specific with the fields that you are appending, post
back. For instance, if the tables don't have the same number of columns in
the same order then you will need to specify the columns in both tables.
The SQL for that would look something like:

INSERT INTO tblDepartments (DPGid , Description , Dept , Post )
SELECT DPGid , Description , Dept , Post
FROM Tbl114