Combine fields from two tables into a single field 1

[mulligh]

I have been reading the FAQ's and don't know the best and easiest way to approach this. Plus, I don't have a lot of experience writing SQL statements, so would appreciate some help.

I have a table "Main" that has a one-to-many relationship with table "tbl_names". They are linked on field "ID". I want to combine all of the corresponding records in tbl_names.name into Main.combined_names where ID is the same. I would like each name to be delimited with a line break. Could someone help me with the SQL, etc.? Thanks a whole bunch.

Example:

table "Main":
ID Description
1 This is the description
2 This is another description

table "tbl_names"
ID Name
1 Smith, John
1 Rocket, Johnny
1 Violet, African
2 Doe, Jane
2 Doe, John

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mulligh]

Thanks for the tip...I have already seen this but am unclear how to construct the SQL statement for my database. I was also wondering whether I could just use the INSERT INTO SQL statement instead?

 

[PHV]

SELECT ID, Description, Concatenate("SELECT Name FROM tbl_names WHERE ID=" & [ID]) As combined_names
FROM Main

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OOps, missed the line break:
SELECT ID, Description
, Concatenate("SELECT Name FROM tbl_names WHERE ID=" & [ID], Chr(13) & Chr(10)) As combined_names
FROM Main

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mulligh]

Thanks for your help...I'm getting closer...

When I run the query, I get an error 3061 "Too few parameters. Expected 1"

Here's the module I'm using (comments removed)

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[mulligh]

And I forgot to add that when I debug the error, it opens VB and highlights this line:

Set rs = db.OpenRecordset(pstrSQL)

 

[PHV]

And what is the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mulligh]

Thanks for your continuing assistance...

SELECT ID, [Description of image], Concatenate("SELECT Name FROM tbl_names WHERE ID=" & [ID],Chr(13) & Chr(10)) AS combined_names
FROM Main;

Note that [Description of Image] is the fullname of the field "Description" that I gave in my example.

 

[mulligh]

OK, stupid me, I had the wrong field name in SELECT, should have been "Names" instead of "Name".

 

[mulligh]

After fixing this stupid mistake, the query ran great (of course). Thank you so much for your time and help, I REALLY appreciate it.