Question Merging Data from multiple tables into single column

[insamoufonyx]

Hi All,

Just registered as im doing some work on MS Access at work, ive got some basic knowledge and im fairly switched on when it comes to IT etc,

Ive created a access database containing multiple tables, theres one main table that will contain all the data compiled.

I then have other tables that contain some matching data but with an additonal column filled in,

I want to be able to pull the data from these other tables into the main table and compile it including merging some data into a single feild.

Effectively this is what the inputs will be:

Table 1
Ident Number            Solution                TRN              
T0941A1                 SLN019149           EFE0008
T0941B1                 SLN019149           EFE0008
T0941C1                 SLN019149           EFE0008

Table 2
Ident Number           Solution                 TRN
T0941A1                 SLN019149           EFE0167
T0941B1                 SLN019149           EFE0167
T0941C1                 SLN019149           EFE0167

Table 3
Ident Number           Solution                 TRN
T0941A1                 SLN019149           EFE0188
T0941B1                 SLN019149           EFE0188
T0941C1                 SLN019149           EFE0188

Then the main table i want to compile the data like this:

Table 4
Ident Number           Solution                 TRN
T0941A1                 SLN019149           EFE0008,TRN0167,EFE0188
T0941B1                 SLN019149           EFE0008,TRN0167,EFE0188
T0941C1                 SLN019149           EFE0008,TRN0167,EFE0188

i want it to show as 1 line item and compile the TRN numbers into a single feild per row rather than having duplicates.

 

[Poppa Smurf]

You will need to create a query and link the IDent Number and Solution in each table then concatenate the TRN of each table.

I have attached an example, open the query qry_result.