By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

Exponential growth in a combining of records

P: 57
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 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
  1. Public Function Concat(strStock_ID As String, _
  2.                        strAttribute As String) As String
  4. Static strLastStock_ID As String
  5. Static strAttributes As String
  7.     If strStock_ID = strLastStock_ID Then
  8.         strAttributes = strAttributes & "-" & strAttribute
  9.     Else
  10.         strLastStock_ID = strStock_ID
  11.         strAttributes = strAttribute
  12.     End If
  13.     Concat = strAttributes
  14. End Function
Here is the query code.
Expand|Select|Wrap|Line Numbers
  1. SELECT tblStockHeader.Stock_ID, (Concat([tblStockHeader].[Stock_ID],[qryListPlayerAttributes].[SubFormValues])) AS Total_Player_Attributes
  2. FROM tblStockHeader LEFT JOIN (tblStocksubPlayers LEFT JOIN qryListPlayerAttributes ON tblStocksubPlayers.Player_Name = qryListPlayerAttributes.Player_Name) ON tblStockHeader.Stock_ID = tblStocksubPlayers.Stock_ID
  3. 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?
Jan 28 '10 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 100+
P: 2,321
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.
Jan 29 '10 #2

Expert Mod 15k+
P: 31,494
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.
Jan 30 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.