Multiple Tables into One Field

When appending fields from multiple tables into one field you wish to combine the following fields from different tables into the one field.

table#1fields: 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 have successfully appended the fields from multiple tables into one field, as requested.

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