Show AllShow All

Combine all the data from two tables

If you want to combine all of the data from two tables that have similar but not identical data— such as two lists (tables) of customers that each have different information (fields) for each customer, and each list has some customers that aren't included on the other list— you can create a query that includes all of the customers. This type of query is called a full outer join. Learn about full outer joins.

  1. In Microsoft Query, create a query and add both tables to it.
  2. Join a field that matches between the two tables (for example, the Customer ID field in two customer lists) by dragging the field in the first table to the second table.

    Choose a field that has information for every record in each table. For example, if two customer lists have matching Customer ID fields, join the Customer ID field.

  3. Double-click the join line between the two tables.
  4. In the Joins dialog box, click option 2, click Add, and then click Close.
  5. Add the fields that you want to the query.
  6. On the View menu, click SQL.
  7. Select the data in the SQL statement box, press CTRL+C to copy it, and then click OK.
  8. Double-click the join line again.
  9. In the Joins dialog box, click option 3, click Add, and then click Close.
  10. On the View menu, click SQL.
  11. Click at the end of the data in the SQL statement box, press ENTER, type the capitalized word UNION and then press ENTER again.
  12. Press CTRL+V to paste the copied information after the word UNION.
  13. Click OK, and when prompted that the query can't be displayed graphically, click OK again.