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 tblPlayerAttributes (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_Attributes = 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:
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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]));