String Aggregation

[grifter]

Hi

I am a newbie here and a newbie in Access. I am trying to group a set of records and concatenate strings of related grouped by columns and have had no luck doing this in access, I can do fine in SQL server with CROSS APPLY function but have been struggling for about 3 days now trying to get VBA or alternative Access SQL code to do the string aggregation.

So here is a sample of my data:

ID         BU       PL        Value
=====  ===== ===== =====
1           BU1     PL1      10
1           BU2     PL1      20
2           BU1     PL2      10
2           BU1     PL1      30

and I want this:

ID         BU                  PL              Total
=====  =========  =======   =====
1            BU1, BU2     PL1, PL1   30
2            BU1, BU1     PL2, PL1   40

to cut a long story short I have found some code that I have been playing about with but not sure why it is not working.


This is my query in Access:

SELECT p.[row id], MAX(Concat(p.[row id], Nz(p.[global business unit], ''))) FROM Products AS p group by p.[row id];

And this is my code:

 Public Function Concat(strRowId As String, strBussUnit As String) As String             
Static strLastRowId As String         
Static strBussUnits As String                 
If strRowId = strLastRowId Then             
strBussUnits = strBussUnits & ", " & strBussUnit            
Else strLastRowId = strRowId             
strBussUnits = strBussUnit        
End If         
Concat = strBussUnits         
End Function

And when I run this I get the following error:

reserved error (-1038); there is no message for the error

If I remove the MAX operator then I do not get the error but my data, instead of presenting the way I want seem to be not quite what I want. To be honest this code is built from a suggestion given on the web and I do not know if it is exactly what I should be using but the problem the person had was exactly as I had so I employed the code and plugged in my own variables/names etc.

But just now I cannot get it to work. I have debugged the code and the error happens after exiting the function so I do not know what is the problem.

I hope someone can suggest either alterations to this code, help me understand it a bit more, or suggest a new way of doing this.

If you do not understand something I am trying todo please ask and I will try to clarify.

Thanks

G