Concatenate values from detail section in group header

[Noruen]

Hi!

I’m just wondering if there is any way how to use some kind of “ConcatRelated” function (you know, from Allen Browne) in report so that values from detail section would concatenate in particular header. I mean, if Access can sum values, show me the first or last value, etc, there must be some way how to get those data and use it for this dirty nasty operation (other than to create another underlying query as source for ConcatRelated function).

thanks

 

[oleronesoftwares]

Yes it is possible.

In the header section, put a textbox, then go to its control source

Go through access objects in the expressionn builder, locate reports, then the name of the current report you are working on.

You will see the names of all objects in the current report, bound the textbox to the controls in the current report that you want to concatenate and concatenate the controls into a single value.

 

[theDBguy]

Not sure there is one built-in. Have you tried using Allen's function in the source query.

Just for reference, here's another simple function that does the same thing.

Code Snippets

 

[arnelgp]

you can Concatenate the fields on the Report's Load Event:

Private Sub Report_Load()
    Dim strConcat As String
    With CurrentDb.OpenRecordset(Me.RecordSource, dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            strConcat = strConcat & ![TheFieldHere] & ", "
            .MoveNext
        Loop
        If Len(strConcat) Then
            strConcat = Left$(strConcat, Len(strConcat) - 2)
        End If
        .Close
    End With
    Me!Auto_Header0.Caption = strConcat
End Sub

 

[Noruen]

Thanks all of you, but what I’m trying to reach is that in every group it will concatenate values from detail section, not all values in header or concatenate values from different fields.

 

[Pat Hartman]

I'm having trouble figuring out why this would be useful since the detail is right there but, create a query to do the concatenation and join that query to the RecordSource query on the field used for grouping. You can then select the concatenated string and bind it to a control in the group header.

If you wanted this in the group footer, you could calculate it with code in the detail section that gets cleared in the group header. This is SIGNIFICANTLY more efficient since it can be done in the normal single pass of the recordsource that creates the report. Putting it in the header means you need values that haven't been read yet so you need the extra query with the join.

 

[theDBguy]

...but what I’m trying to reach is that in every group it will concatenate values from detail section,

But isn't that what @arnelgp and I gave you? Did you give them a try?

 

[arnelgp]

Thanks all of you, but what I’m trying to reach is that in every group it will concatenate values from detail section, not all values in header or concatenate values from different fields.

you are not giving any "specifics", so we are shooting randomly here.
just be specific, concatenate on what group, what fields to concatenate, what is the sample
display will look like?

 

[Noruen]

I thought this kind of feature would be great for such things like when you have (limited number) of tags for example: so that tags that would be listed one by one on separate rows in detail section would be concatenated in particular group header.

@Pat Hartman so it means values from recordset are somehow available to do it in the footer?

@arnelgp concatenate for each group, string-type fields

for example:

1.12.2021 (GROUP)
Dinner
Gym
Date
Restaurant
2.12.2021 (GROUP)
Vacation
Coffee

Be like:

1.12.2021, Dinner; Gym; Date; Restaurant
2.12.2021, Vacation, Coffee

… with hidden detail section of course

I can do this quite easily with ConcatRelated or custom separate-query function but what I need / asking you is if I can utilize already existing report source values.

 

[MajP]

I would build a more custom function. Should be pretty easy.

Public function ConcatRelated(GroupID as string) as string
end function

So you pass in 1.12.2021 and it returns: Dinner; Gym; Date; Restaurant
In the header in an unbound textbox
=ConcatRelated([GroupField])

I can do this quite easily with ConcatRelated or custom separate-query function but what I need / asking you is if I can utilize already existing report source values.

Not sure what that means. If you know how to do it already, but wanting to know if there is a built in function? No there is not built in way to do that, if that is what you are asking.

 

[Pat Hartman]

@Pat Hartman so it means values from recordset are somehow available to do it in the footer?

A report is a sequential process. Record 1 is read and formatted. Record 2 is read and formatted, Access keeps track of when to print headers and footers.

The point I was making is that when you are printing the group header, only the FIRST record of that group has been read so you can't accumulate the values because you haven't read them yet so you would have to run a separate query to get the values for that group and concatenate them which is a really inefficient process especially when what you re printing in the header is going to be printed just below in the detail section. By the time you get to a group footer, all the records of that group have been read so if you were concatenating them as you read them, you could print them as a recap in the footer.

Do what you want however long it takes. I was just trying to make you understand the actual work that will go into producing this duplicate list.

 

[Noruen]

I would build a more custom function. Should be pretty easy.

Public function ConcatRelated(GroupID as string) as string
end function

So you pass in 1.12.2021 and it returns: Dinner; Gym; Date; Restaurant
In the header in an unbound textbox
=ConcatRelated([GroupField])


Not sure what that means. If you know how to do it already, but wanting to know if there is a built in function? No there is not built in way to do that, if that is what you are asking.

Sorry, I probably didn’t made myself clear.

I know there is not built-in fuction for that. But Access has functions like “First” that will choose first value in group or Sum that will sum up all values in group, etc. So the problem I’m asking about is how to write function that will instead of summing or choosing the first value in group concatenate all values from group.

I know how to do it based on Allen Browne’s function where there is separate query initiated / different recordset. But again, I’m looking for the way how to do it with current recordset - if it is possible.

BTW how that function you posted above works?

 

[Noruen]

A report is a sequential process. Record 1 is read and formatted. Record 2 is read and formatted, Access keeps track of when to print headers and footers.

The point I was making is that when you are printing the group header, only the FIRST record of that group has been read so you can't accumulate the values because you haven't read them yet so you would have to run a separate query to get the values for that group and concatenate them which is a really inefficient process especially when what you re printing in the header is going to be printed just below in the detail section. By the time you get to a group footer, all the records of that group have been read so if you were concatenating them as you read them, you could print them as a recap in the footer.

Do what you want however long it takes. I was just trying to make you understand the actual work that will go into producing this duplicate list.

And I appreciate it, there was not intention to be offensive in any way from my side!

I just don’t know how to concatenate values in footer anyway…

 

[MajP]

I will demo a quick version. That was not a function, just showing what to push in and out.

 

[Noruen]

My thoughts were something like

Public Function ConcatValues(strField As String)
Dim rs As Recordset
Dim strResult As String

Set rs = Report.Recordset

For Each rs!TagName
    strResult = strResult & "; " & rs!TagName
    
    Loop
    
    etc...

However I have no idea how to do it with current recordset neither how to tell the function to do it only with particular group of values…

And yes, that VBA code is stupid and nonfunctional, it is just for demonstration purposes

 

[MajP]

So i make a tailored function.

Public Function ConcatComplaints(CustID As Variant) As String
  Dim rs As DAO.Recordset
  If Not IsNull(CustID) Then
    Set rs = CurrentDb.OpenRecordset("select * from tblComplaints where customerID = " & CustID)
    Do While Not rs.EOF
      If ConcatComplaints = "" Then
        ConcatComplaints = rs!ComplaintDate
      Else
         ConcatComplaints = ConcatComplaints & "; " & rs!ComplaintDate
      End If
      rs.MoveNext
    Loop
  End If
End Function

If I pass in the customer id from the header it returns the dates of complaints for that customer.

 

[Noruen]

So i make a tailored function.

Public Function ConcatComplaints(CustID As Variant) As String
  Dim rs As DAO.Recordset
  If Not IsNull(CustID) Then
    Set rs = CurrentDb.OpenRecordset("select * from tblComplaints where customerID = " & CustID)
    Do While Not rs.EOF
      If ConcatComplaints = "" Then
        ConcatComplaints = rs!ComplaintDate
      Else
         ConcatComplaints = ConcatComplaints & "; " & rs!ComplaintDate
      End If
      rs.MoveNext
    Loop
  End If
End Function

If I pass in the customer id from the header it returns the dates of complaints for that customer.

Thanks @MajP

However you use custom recordset in your function. Is there any way how to use Report Recordset?
Maybe I need to be more specific: When I put SUM to report group footer Access will sum just values of specific group. Something like SUMIF analogy from Excel. So is there any way to not sum those values but concatenate them into the string?

I still feel like we are talking about different things not understanding each other.

Another reason why I ask you to explore this possibility is that with ConcatRelated function that uses custom recordset, I have performance issues. I want to believe this solution would speed up things a little

 

[MajP]

You could pass the recordset in, but so what? So the way i have it you traverse only the records in the group. You would then traverse all the records. Why do you think that is better? There are tricks for speed. If you can keep the rs open that helps a lot but I can not do a generic function for that. So it may be quicker to pass the recordset because you are not opening and closing, but you are dealing with more records.

 

[MajP]

When I put SUM to report group footer Access will sum just values of specific group. Something like SUMIF analogy from Excel. So is there any way to not sum those values but concatenate them into the string?

The answer is no. There is no way to make a generic function that you can put in an unbound control that will concatenate the records. So I cannot write a function ConcatRecords, and put that in a group footer/header =concatRecords([somefield]). However, as I have clearly shown you can write a dedicated function (not generic) that will work and accomplish this. I am very confident I can write very efficient code that can do this. I can do this using the reports recordset. I just cannot do it in a single generic function that works in all reports. I agree there are limitations with the allen browne approach for many applications. Using a concat function in a query is fine for only a small amount of records. It is fine for a single form view. If you need to do this over a large amount of records, I usually resort to a temp table.
Maybe if you have a specific example that would help, since I already clearly shown it can be done, but that did not seem sufficient.

 

[arnelgp]

here is a sample report that uses the report's recordsource.