Exponential growth in a combining of records

[HSXWillH]

I have made a lot of progress in this entire thing thanks help from NeoPa and others, but I've now come to a problem that is pretty severe. I'm attempting to capture all attributes that pertain to a Stock_ID.

The flow of the db goes tblStockHeader (contains the Stock_ID) has a 1 to many relationship with the tblStockPlayer (contains the Stock_ID & Player_Name. I have tblPlayerHeader (contains Player_ID (auto# PK) and Player_Name (text)) that has a 1 to many relationship with tblPlayerAttrib utes (contains the Player_Name and the PAttr_Desc).

What I'm attempting to do is for each Stock_ID, combine all the PAttr_Desc from each associated Player_Name into a single query field. For instance, if Stock_ID 123 is associated with Ted Williams and Mickey Mantle through the tblStockPlayer, and Ted W has PAttr_Desc of 400 Hitter and Mantle is Triple Crown.....I want a manner to get output of Stock_ID = 123 --- Total_Player_At tributes = 400 Hitter/Triple Crown.

Sorry if that's confusing.

Anyways, I've been given several articles here and have adapted the code but am running into an issue. I believe the root cause is the Static initiation of the variables but I don't know how to fix it. First time you open the query, you get the "400 Hitter/Triple Crown" like I want. Next time you open it, it's now "400 Hitter/Triple Crown-400 Hitter/Triple Crown". Each iteration of opening the query adds another level of the attributes.

Here is the Function Code:

Public Function Concat(strStock_ID As String, _
                       strAttribute As String) As String
                       
Static strLastStock_ID As String
Static strAttributes As String
    
    If strStock_ID = strLastStock_ID Then
        strAttributes = strAttributes & "-" & strAttribute
    Else
        strLastStock_ID = strStock_ID
        strAttributes = strAttribute
    End If
    Concat = strAttributes
End Function

Here is the query code.

SELECT tblStockHeader.Stock_ID, (Concat([tblStockHeader].[Stock_ID],[qryListPlayerAttributes].[SubFormValues])) AS Total_Player_Attributes
FROM tblStockHeader LEFT JOIN (tblStocksubPlayers LEFT JOIN qryListPlayerAttributes ON tblStocksubPlayers.Player_Name = qryListPlayerAttributes.Player_Name) ON tblStockHeader.Stock_ID = tblStocksubPlayers.Stock_ID
GROUP BY tblStockHeader.Stock_ID, (Concat([tblStockHeader].[Stock_ID],[qryListPlayerAttributes].[SubFormValues]));

Is there a way to get away from Static variables in the original function? What would be the best manner to do this?

[TheSmileyCoder]

Declare the variables outside the code with a public scope. Then reset them to "" before running the query. Depending on where you have your query, you could do it several places. If its a form, you could do it onLoad, or OnCurrent for instance.

[NeoPa]

I recognise that code ;) It comes from Combining Rows-Opposite of Union I think.

This was always designed to run within groups of data, where every group caused the data of the previous group to be reset. In your case you're running the same group in a separate run, immediately following on from the group that was last run, thereby avoiding the resetting code.

A very simple way of handling this then, would be to call the function from the code that calls for the execution of the different queries (whether they are run as queries or reports is immaterial) in such a way as to pass a value (as strStock_ID) that is not going to occur in the data naturally. This may be an empty string, or the text "Reset me now - I am done!", it hardly matters. it's simply important that it is not the same as will be used within the query.