Combining tables (2000 SR2)

Topic

New Reply

I have 2 table in Access 2000 that I want to make into one large table. The fields in each are the same. How do I do this?

Reply | Quote

Replies

Just use an append query to add the records from one table to the other. Then delete the table you don’t need any more.

Reply | Quote

Thanks. I really don’t know how to do a append query. I have table1 and table2 and i just want to put them both together. thanks

Reply | Quote

You’re not doing a “join” query. “Join” is a term that describes relating one table to another. What you want to do, I think, is take all the records from one table and actually put them in the other table permanently, right?

If so then you create a new query by clicking on the queries tab and then clicking the New button. When you see the New Query dialog, select the default Design View and click on OK. That will bring up the query grid and a Show Table dialog that allows you to select tables. Select the table you want to copy the records from click Add. Then click the close button.

Now you see the query grid with a table above it. On the menu above, select Query and then click on the Append Query item. That changes the type of query you’re creating and will bring up a dialog where you can enter the name of the table you want to add the records to. You can use the table name dropdown to find the table you’re copying the records to.

After that, you can do one of two things. If all the field names in the two tables are the same, then just click on the asterisk (*) you see in the table and drag that down onto the query grid. That tells Access to append all the records from all the fields into fields of the same name in the table you’re copying to. If the fields don’t have the same name, you can highlight all of the fields except the asterisk and then drag them down onto the grid. If Access finds matching field names, it will put them in automatically. If it doesn’t, you can click on the Append To dropdown for each field and tell it which field in the target table to append the field value to. Be careful here, though. If you used an autonumber key in the tables, you’ll want to leave that field out of the append query. Access will create a new autonumber for the new record when you append it anyhow.

Then just run the query, either by selecting Query–>Run from the menu or by hitting the bang (exclamation point) button on the toolbar.

If you don’t want to actually add the records to the other table, you’ll need to do a union query, which is not as easy. If that’s the case, post again and I’ll explain that one.

Reply | Quote