Many fields from multiple tables into one field

How to get many fields from multiple tables into one field.

This is the problem you have.  You have multple tables, that contain similar information, you wish to concatenate, these fields and combine them all together into the one query and within that query in the one field.

Wow, how does one start with that adding many fields from multiple tables into one field.  First of all, we will set up three make believe tables.

table#1fields: Name, areas, date

table#2

fields: Name, areas, date

table#3

fields: Name, areas, date

The first thing I will show you is how to create a Union Query, we will not be concatenating the fields in the first instance, but will show you how to create a simple Union Query,

You cannot create a Union Query in the normal design grid (but we will use the query design grid to start off).

First of all 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/button, 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 second 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 query.

All working?

Now time concatenate, these three fields into one field.

To do this, we are going to change first SELECT and FROM syntax to the following

SELECT table#1.Name & " " & table#1.areas & " " & table#1.date As NewField

FROM table#1

This creates the a new field called NewField (you can change this to what ever you want) that has the three fields joined together, separated with a space.

Change the next two SELECT and FROM syntaxes as per the first but you do not need to include the 'AS newfield' for these. 

These two will now look like the following:

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;

Once again, close and save your query, to see you now get the three different fields, from three different tables, into the one query and the one field.

Note: Even though in this example we have use fields with the same name from the different tables, they do not have to have the same name. They just have to be of the same data format (i.e. text, number etc), and there has to be the same number of fields from each table.

Hope you have been able to follow these instructions, and can now add many fields from multiple tables into one field in one query.

Concatenate