473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Exponential growth in a combining of records

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
  3.  
  4. Static strLastStock_ID As String
  5. Static strAttributes As String
  6.  
  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
  15.  
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]));
  4.  
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
2 1549
TheSmileyCoder
2,322 Expert Mod 2GB
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
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Mahesha | last post by:
Exponential Moving avg is calculated using the formula. EMA = (Today's Price)* K + (EMA yesterday) * (1-K) where K = 2 / (N+1) The user is going to Input the K. It is something like F(N) =...
8
by: Ilan | last post by:
Hi all I need to add data from two Excel sheets (both on the same workbook) to an existing table in my SQL DB. The problem is that each sheet holds different fields for the same record, though...
9
by: J.Sperlhofer | last post by:
Good morning, Javascript-Professionals. I'm looking for an possibility to show a (calculated) 64bit-Number without exponential notation. I don't want to see exponational notation within my...
4
by: Tony Williams | last post by:
I want to combine two tables into one. I have a table with all the field definitions from two other tables. I now need to update this new table with the data from the other two tables.However both...
4
by: mike | last post by:
I have a database table like the following: id|name|item_id|sequence and the following instance data: 1|Apple|419841|1 2|Orange|419841|2 3|Banana|935890|1 4|Lime|959081|1
2
by: Rod Brick | last post by:
I'm trying to print a Double in straight decimal form, not exponential. I can't seem to accomplish this. This seems like it should be simple enough. The output I'm looking for is "0.00001", not...
1
by: ferraro.joseph | last post by:
Hi, I'm querying Salesforce.com via their AJAX toolkit and outputting query results into a table. Currently, their toolkit does not possess the ability to do table joins via their structured...
2
by: Poz | last post by:
Is there a way to convert an exponential number (held in string format) to non-exponential format without losing any precision in the number? For example, I have a string variable containing...
3
by: Ken Fine | last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets and DataTables should be able to answer fairly easily. The basic question is how I can efficiently match data from one...
2
by: disco990 | last post by:
Hello everyone! I have an assignment in my Java class to gauge and compare the "order of growth" of two different sections of code. Below is the actual assignment: "The following code...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.