Concatenate multiple rows into 1 field

[cdoyle]

Hi,
My database has a 'markets' table, where there could be multiple markets per campaign.

So right now in my report, it's displaying like this

campaign 1
Market 1
Market 2
Market 3
Etc.

Is there a way, to Concatenate multiple rows into 1 field. So it would look something like this

Campaign 1
Market 1, Market 2, Market 3

That would save a lot of space in the report, and make it easier to read.

 

[billmeye]

You can Concatenate the rows using a function like this one:

Function Concatenate(Gettbl As String, GetKey As String, KeyValue As String, FieldConct As String) As String
Dim Rst As DAO.Recordset, MySql As String
Concatenate = ""
MySql = "SELECT * FROM " & Gettbl & " WHERE (((" & Gettbl & "." & KeyValue & ")='" & GetKey & "'));"
Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
If Rst.RecordCount > 0 Then
Rst.MoveLast
Rst.MoveFirst
Do While Not Rst.EOF
    If Nz(Concatenate, "") = "" Then
    Concatenate = Rst.Fields(FieldConct)
    Else
    Concatenate = Concatenate & ", " & Rst.Fields(FieldConct)
    End If
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing
End Function

End Function

To use it, place the function in the control source of your report:

=Concatenate("tblName","PrimaryKeyName","KeyValue","FieldToConcatenate")

Roughly for you:

=Concatenate("Markets","Campaign","Campaign 1","Market")

 

[cdoyle]

You can Concatenate the rows using a function like this one:

Function Concatenate(Gettbl As String, GetKey As String, KeyValue As String, FieldConct As String) As String
Dim Rst As DAO.Recordset, MySql As String
Concatenate = ""
MySql = "SELECT * FROM " & Gettbl & " WHERE (((" & Gettbl & "." & KeyValue & ")='" & GetKey & "'));"
Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
If Rst.RecordCount > 0 Then
Rst.MoveLast
Rst.MoveFirst
Do While Not Rst.EOF
    If Nz(Concatenate, "") = "" Then
    Concatenate = Rst.Fields(FieldConct)
    Else
    Concatenate = Concatenate & ", " & Rst.Fields(FieldConct)
    End If
Rst.MoveNext
Loop
End If
Rst.Close
Set Rst = Nothing
End Function

End Function

To use it, place the function in the control source of your report:

=Concatenate("tblName","PrimaryKeyName","KeyValue","FieldToConcatenate")

Roughly for you:

=Concatenate("Markets","Campaign","Campaign 1","Market")

thanks for the help, I'm trying to make this work now.
What is 'KeyValue'?

The report is based off a query that joins my campaign table with the market table, and lookup table. Here is the SQL

SELECT tblCampaign.ID, tblCampaign.[Campaign Name], tblCampaign.Description, [Markets-lookup].Market
FROM [Markets-lookup] INNER JOIN (tblCampaign INNER JOIN [tblecampaign-markets] ON (tblCampaign.ID = [tblecampaign-markets].[campaign-id]) AND (tblCampaign.ID = [tblecampaign-markets].[campaign-id])) ON [Markets-lookup].ID = [tblecampaign-markets].[market-id];

So I'm not quite sure which tables/fields I need to include in the code you provided.

 

[billmeye]

If you are going to concatenate all rows in the Markets table for every Campaign, you would not want to join the tables in a query.

I've attached an example, take a look and see if that is what you want and adapt it to your tables.

 

[cdoyle]

hmm, I'm not sure if that fits my table structure.
The campaigns could have multiple markets, but the markets could be for multiple campaigns too.

so
Campaign 1 US, UK,
Campaign 2 US, TW, UK
Campaign 3 UK, TW

I'm not sure that works with the structure in your example.
Is there a way to make your code work with my table structure?

 

[cdoyle]

shoot, I just remembered my tables are linked tables.

I've converted them to local tables, so you can see the design.

 

[billmeye]

You are going 3 tables deep with your lookup to return the text equivalent of the Market so the function although setup to be mostly general, needs to have table specific code to make things work. Take a look at Query1 and let me know.

 

[cdoyle]

You are going 3 tables deep with your lookup to return the text equivalent of the Market so the function although setup to be mostly general, needs to have table specific code to make things work. Take a look at Query1 and let me know.

Thanks, this is getting me closer. I'm looking at the query1, and I can see how it combines the markets. But when I try and use that query in the report, it treats them as separate records still. Not quite sure why, seems like that should have worked.

Next question, I'm going to need to do this on several fields as I build the DB and reporting. Can I just add to the function at this spot

Case "Market-id" 'Looking for Markets from Market-Lookup table
If Nz(Concatenate, "") = "" Then
    Concatenate = DLookup("[Market]", "Markets-lookup", "[ID]=" & Rst.Fields(FieldConct))
    Else
    Concatenate = Concatenate & ", " & DLookup("[Market]", "Markets-lookup", "[ID]=" & Rst.Fields(FieldConct))
    End If
Case Else 'If looking for something else

at the else if, basically copy the code but substitute for the other tables/fields I need to merge

 

[cdoyle]

OK, nevermind my question about the report.
I just tried it again, and now it's working.

But for doing this for other tables, am I correct about adding the code within the Else if?

 

[billmeye]

The function is generic and will work with other tables the only detail is to determine what the specific data you want to concatenate. The function is fed the subtable name (many side table), the primary key name of the master table (the one side table), the primary key value (if used in a query that would be each row from the one side table) and either the field in the subtable to concatenate or the ID of the lookup value in the lookup table.

The changes you need to make are in this last part, you need to add a new case statement:

Case "Market-id" 'Looking for Markets from Market-Lookup table
If Nz(Concatenate, "") = "" Then
    Concatenate = DLookup("[Market]", "Markets-lookup", "[ID]=" & Rst.Fields(FieldConct))
    Else
    Concatenate = Concatenate & ", " & DLookup("[Market]", "Markets-lookup", "[ID]=" & Rst.Fields(FieldConct))
    End If
Case Else 'If looking for something else
'ADD NEW CASES FOR THE FORTH VARIABLE

 

[cdoyle]

Thanks for all your help
I think I've gotten it all working!

 

[billmeye]

That's good to hear.