Web Development

Ask something
Discussion Locked

Merging two tables with single columns i

0 helpful
7 replies

by chuxiz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I created two tables in Access 2000 each with a single column. I want to merge the two columns in both tables into a single table with both columns. How can I do this? pls assist

7 replies

by sl-campbell

Use a Make-Table query:

Go to the query window. Click on Design View. A pop-up window will display - add the 2 tables in question (if the window isn't showing then click on the menu: QUERY|SHOW TABLES). Close this pop-up window.

You will see 2small windows, each one representing one of the tables. Find the columns in the first table that you want and double click on the column names. Do the same for the second table. The column names must show now in the grid below.
Go to the menu item:QUERY|MAKE-TABLE QUERY. Enter the new table name and click "OK".

Now click on the icon with the Red exclamation mark or on the menu item: QUERY|RUN

Confirm the message box with YES

by sl-campbell

If you are talking about combining the data from both tables into one table, with the results all being in one column, and the field datatypes are the same, then do the same that I told you above, but only select one table, and execute it. Then create another query selecting only the 2nd table and click the menu QUERY|APPEND QUERY and select the new table created with step #1. This will append all the records.
You could also do this by manually creating the new table (click on the 1st table and press Ctrl-C then Ctrl_V and give a name) opening the second table and press Ctrl-A and then open the new table, click on the last (new - empty) line so that it is marked, and then press Ctrl-V. The records will be pasted.

by sl-campbell

By the way, if you still want 2 columns, and they are in each table already in the correct order, then just manually create the new table with the two fields of the same type as in each original table and open it up. Then open the first table, clickon the column header to mark the column, press Ctrl-C, go back to the newly created table, click on the column header where you want the records to go and press Ctrl-V. The do the same for the 2nd table, copying that column and pasting the date to the 2nd column in the new table.

by glen_mcleod

Without a join column between the tables, answer 1 will give you a cartesian product.

Is this a one-time operation? If so, I would export each table to an Excel apreadsheet, then copy one column next to the other and import the result back into Access.

You also don't mention if the data in the tables match up with each other, i.e. record 1 of each table go together and so on. That could be problamatic.

Glen

This discussion is closed to new replies.