Concatenate values from detail section in group header

[MajP]

Just understand Allen Browne's function, my original function, and ArnelGp's solution are really all the same. Just slightly different aproaches to creating the recordset, but they are still creating a new recordset in each call to the function. There is IMO no way to use the actual reports recordset, I do not think a report even exposes a recordset property.
My final solution is however different in that I open the recordset and leave it open, instead of continously opening and closing a recorset. This is faster than opening and closing on each function call.

Private rs As DAO.Recordset

Private Sub Report_Load()
  Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)
End Sub

Private Function ConcatName(GroupID As Integer) As String
  'Exit Function
  rs.FindFirst "groupID = " & GroupID
  Do While Not rs.NoMatch
    If ConcatName = "" Then
      ConcatName = rs!Full_Name
    Else
      ConcatName = ConcatName & "; " & rs!Full_Name
    End If
    rs.FindNext "groupID = " & GroupID
  Loop
End Function

When the report loads it creates a recordset based off the reports recordsource, and leaves it open. Then each call to the concatenate does not have to create a new recordset.
However, speed is relative. I used @arnelgp solution on the large report and it was a few seconds slower, but still not bad for 500 groups. It this was thousands of groups it would make a difference.

 

[Gasman]

So in this example I have 10k records. I concatenate every group of 20 records. So that is 500 group concatenations. The report runs instantaneously. So what exactly is your performance concern? In this case I concat each person in the group.
NO you cannot do it the way you want. YES you can do it easily and efficiently.
View attachment 96707

@MajP Excellent solution
However as the file uploaded, it produces the following, not what you displayed?

I know the reason why, but just mentioning it for the OP should they use your method.

 

[The_Doc_Man]

I do not think a report even exposes a recordset property.

Actually, it IS listed, as is the .RecordSource property.

Report.Recordset property (Access)

Office VBA reference topic

docs.microsoft.com

However, unlike forms, reports do not offer a .RecordSetClone option. That is what you would want to use for auxiliary lookups such as are needed by the functions you are describing. Which is why you have to "roll your own clone" for this kind of enumeration. The problem isn't that the data set isn't available - it is in use at the moment and THAT is why the detail enumeration won't work straight up.

My basic question was if I can use underlying recordset to perform concatenate function.

@Noruen - the problem is not that you can't use data in the report's recordset. It is that the report's logic flow depends on the sanctity of that recordset while the report generator is running. To do what you want would break that sanctity and scramble the recordset, probably at the very least causing the report to lose all cohesion and omit some things or double-dip on some things. You can only have one active bookmark in a recordset and the report is using it at the moment. You can only have one cursor (the more common industry term for the concept of "where is the recordset pointing right now?") That's why this is such a difficult proposition. No clone, a single bookmark, a single recordset, and reports are not inherently designed for that kind of "sneak peek" or "sneak review" operation.

Access reports are very much like the original Alan Turing "automaton" that sets up a sequence of events based on the "input stream" of a formal automaton mode. A report doesn't remember stuff from one detail section to the next. It evaluates where it is and what it needs at the moment for each section as it comes along and based on the instructions involved. What your "detail enumeration" would require is that the report would retain copies of everything it had seen so far. But the report generator isn't programmed that way. Which is why we need EVENT code when we want Access to do something it wasn't designed to do.

 

[Gasman]

Now somewhat puzzled, as that is what @MajP used for his function, the reports's recordsource ?

 

[MajP]

Now somewhat puzzled, as that is what @MajP used for his function, the reports's recordsource ?

The question is can you use the Recordset directly from the report vice opening a second recordset. My solution and @arnelgp solution used the report's recordsource but created a new recordset in code. Also you are correct I forgot to add the "Order by Full_Name" clause before I uploaded the demo.
@The_Doc_Man. The recordset property of the report is only available to an .ADP and thus of no use. Give it a try.

 

[MajP]

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

As said you can do this in the footer without any recordset. The code is like this for my example. So I guess this is what you would call "using the reports recordset."

Private Concat As String
Private TheGroupID As Variant
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   If Me.GroupID <> TheGroupID Then
    Concat = Me.Full_Name
    TheGroupID = Me.GroupID
  Else
    Concat = Concat & "; " & Me.Full_Name
     Me.txtConcat = Concat
  End If
End Sub

As @Pat Hartman said this in theory should be efficient since as the report paints each line you simply concatenate. In reality it is way slow. I doubt the code is slow, but the formatting of the report is and this probably causes multiple reformats of the footer section. I tried moving to other events and it was still slow.

 

[The_Doc_Man]

The question is can you use the Recordset directly from the report vice opening a second recordset. My solution and @arnelgp solution used the report's recordsource but created a new recordset in code. Also you are correct I forgot to add the "Order by Full_Name" clause before I uploaded the demo.
@The_Doc_Man. The recordset property of the report is only available to an .ADP and thus of no use. Give it a try.

I'll easily believe it is restricted, PRECISELY because Access wants it to be sacrosanct and disallow people from diddling with where the recordset currently points via its bookmark. The .Recordsource is, however, available, isn't it? However, if you look at the reference I linked, that reference says that a native Access ADO or DAO recordset IS read/write in report context. It is only SQL-based DAO.Recordsets that are not available. Look in the middle of the article, there is even a purple note about ADO recordset restrictions just above the section to which I refer. So I didn't come up with my comment "out of the blue."

I'm well aware of the possibility that some properties are not available in various conditions. The most common one is the .Text property for text boxes and other text-oriented controls, only visible when that control is in focus. Selective property availability is rampant in Access.

 

[Noruen]

@Pat Hartman I will try both solutions just on tomorrow

this is quite interesting discussion I can’t probably fully appreciate, but it is amazing how deep into Access you see!

If I may, @MajP @The_Doc_Man & @Pat Hartman: I have had no idea Report works differently than underlying Query (to my understanding, Report was just Query-prettyfier). So, would this act of value concatenation be easier in Query itself? Because as I have read some articles about specific Access functions, when i use First() or Last() in aggregate query, it looks to the datase it has to GROUP BY and selects the first or the last value of tht field for particular group. Would this “group dataset” be available for contacenation with potential to be even faster than solution @MajP posted?

 

[The_Doc_Man]

You may find that First() and Last() do not do exactly what you want if you only have GROUP BY. For First() and Last() to provide "commonly expected" behavior, you need an ORDER BY as part of the mix. This is because in the absence of an ORDER BY clause, you cannot predict which record would be First() or Last() for any data set. The underlying table is an unordered set of records. Grouping only changes this problem slightly by breaking down the underlying table into groups - but within each group, there is still no guaranteed order without that ORDER BY.

The reason reports can be so vexing is that they want to impose specific order on your input recordset no matter WHAT its source (tables, queries, multi-layered queries.... all the same to the report), so they "deconvolute" it and "adjust" the data stream to match the grouping and sorting options you specified to the report builder. In other words, they override the actual .Recordsource order - which is why MajP said that property wasn't available. The report isn't necessarily using it as it was originally written and it doesn't show you what it IS using.

Your problem is that you appear to want both the detail sections AND the concatenated data in headers or footers. Reports give you headers and footers, and you can write VBA to build a string based on code in the Group Header, Group Footer, and Detail Format events. Presenting that concatenation in the appropriate Footer is easy enough. Putting it in the header requires you to be prescient. But of course, Access doesn't come with a built-in crystal ball.

Doing this formatting in a query faces the OTHER problem - that you can't so easily put headers and footers in the datasheet view of a query. There is also no place for code to run unless you create a public function to be called in a query, but there is also no way to put it anywhere different from the other detail records.

Thus, you are trying to get the best of both worlds but asking for something that exists in neither. The saving grace is that with VBA, you can add some event code to get very close to where you want to go.

 

[MajP]

Would this “group dataset” be available for contacenation with potential to be even faster than solution @MajP posted?

1. No. 2. Does not even make sense.

 

[MajP]

So, would this act of value concatenation be easier in Query itself?

Most examples you see of concatenation use the query itself. The problem with most implementation of this solution you call the function for each record which requires a new recordset to open and close for each call. Works fine on small data sets. This can be very slow on big ones. The example opens the RS once and moves within it. That is why this is probably much faster in many cases. The code in all cases is about the same.

 

[Noruen]

Ok, thank you all for explanation! I hope I’m smarter and now I will go find that discussion with TheDocMan. I’m quite curious.

btw @MajP I tried yours and @arnelgp solution today and it seems they, at least now, are comparable in speed and uncomparably faster than Allen Browne’s. That open/close recordset really makes the difference.