Append Fields from Different Tables into the One Field (Union Query)

 For this example, you wish to combine the following fields, from different tables into the one field.

table#1

fields: Name, areas, date

table#2

fields: Name, areas, date

table#3

fields: Name, areas, date

To do this you will need to build a union select query. You cannot do this in the normal design grid (but will use the query design grid to start off).

Build a simple SELECT query by following the query wizard, just adding table#1 and the three fields.

Open this query in Design view then, on the View tab, select SQL view and the SQL view for the query will open. It will look like this (or similar):

SELECT table#1.Name, table#1.areas, table#1.date

FROM table#1;

Copy these two lines of code and paste beneath;

Change all of table#1 to table#2, remove the ; from the second line of code (in both the original and the copy);

Add UNION to the beginning of the SELECT line;

Repeat for Table#3.

Your syntax should end up looking like the following:

SELECT table#1.Name, table#1.areas, table#1.date

FROM table#1

UNION SELECT table#2.Name, table#2.areas, table#2.date

FROM table#2

UNION SELECT table#3.Name, table#3.areas, table#3.date

FROM table#3;

Close and save your query and run it to check you get the results you expect.

Do you like this tip? Subscribe to my weekly Newsletter to receive tips weekly via email. Click Here to subscribe.