469,333 Members | 4,210 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,333 developers. It's quick & easy.

Split a Text String and Append to other Table.

I have a table entitled Stock_Catalog. Fields include Stock_ID (random autonumber), Brand, Card_Number and Player_Name. Most of the entries have 1 name in the Player_Name field, but some show multiple players and have entry format such as Warren Spahn/Jim O'Toole/etc....

What I currently have is upon closing out the Stock_Catalog entry form, code runs to append the Player_Name to a table titled Player_Hdr where I then add details about the player.

What I want to do is figure out a manner to split that Warren Spahn/Jim O'Toole/etc.... input into multiple records in the Player_Hdr table. So the code would run and add Warren Spahn as a new record, Jim O'Toole as a new record etc etc. And I don't know how to accomplish this or even exactly where to start. I do know that the number of names in each record could be variable, mostly 1 name, but potentially up to 8 in rare cases.

Here is my existing VBA code to append Player_Name to the Player_Hdr table. Players_Hdr is set up to not allow duplicate Player_Name entries.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  3. Dim Player_Hdr As String  
  5. Player_Hdr = "INSERT INTO P_Hdr ( Player_Name ) SELECT Stock_Catalog.Player_Name FROM Stock_Catalog WHERE (((Stock_Catalog.Player_Name) Is Not Null))"
  7.     DoCmd.SetWarnings False
  8.     DoCmd.RunSQL Player_Hdr
  9.     DoCmd.SetWarnings True
  11. End Sub
I'm pretty new at VBA coding, at least seeing what does what, so I might not fully understand whatever answers come back initially so please bear with me.
Dec 23 '09 #1
5 3721
904 Expert 512MB
You could use the SPLIT function using the "/" character as your delimiter or better yet you could design your tables properly so you do not have this problem.

It is a sin in database design to have more than one result in a field per record. Read up on normalization and fix your table design.

Dec 23 '09 #2
Then if I create the player_association table with the player_name and the stock_id for each player...how would I then combine all associated names into one full_description for each item?

So a id of 1234, with a brand of Topps, a card_number of 001 (text format because many cards are alpha-numeric) that has 2 players, say Ryan and Ripken. How would I then get a full_description in a query of #1234 - Topps #001 -- Ripken/Ryan?
Dec 23 '09 #3
904 Expert 512MB
It's called a bridge table. Figure out your business rules and design your tables based on normalization. Read here about normalization.

Give it a try and let me know your new design and I will then direct you if you are having trouble.

Dec 23 '09 #4
32,182 Expert Mod 16PB
That's good advice Will.

It's much easier in a query to join things together (even in a list - see Combining Rows-Opposite of Union) than it is to have an RDBMS work on data held within a single field. Imagine particularly how unlikely it is for any optimisations to be done on data stored as sub-items within a field?

The linked article (Normalisation and Table structures from MSHMYOB's post) is actually well worth a read. It's also phrased very readably. Not too difficult for experienced and newbies alike to comprehend.
Dec 24 '09 #5
Okay, here is what I've got after a few days of trial and error (and one persistent bug):

tblStockHeader = Stock_ID (auto# pk), Card_Year, Brand, Subset
tblStockPlayer = SPlayerID (auto# pk), Stock_ID, Player_Name
tblPlayerHeader = Player_ID (auto# pk), Player_Name, DoB
tblPlayerAttributes = PAttr_ID (auto# pk), Player_ID, PAttr_Desc

2 Forms are:

frmStockHeader = tblStockHeader with subform frmStockPlayer where Stock_ID carries through. Logic is a card can have multiple people on them so each is it's own record line.

frmPlayerHeader = tblPlayerHeader with subform frmPlayerAttributes where Player_ID carries through. Logic is Cal Ripken can have 2+ attributes such as Hall of Fame and 3000 Hits plus other.

I have set up a module using the same syntax from the link you provided about joining in a non-union format. Here is that code:

'Combination returns a list of the attributes so far found for the current ID.

Expand|Select|Wrap|Line Numbers
  1. Public Function Combination(strID As String, strAttribute As String) As String
  3.     Static strLastID As String
  4.     Static strAttributes As String
  6.     If strID = strLastID Then
  7.         strAttributes = strAttributes & "/" & strAttribute
  8.     Else
  9.         strLastID = strID
  10.         strAttributes = strAttribute
  11.     End If
  12.     Combination = strAttributes
  14. End Function
When I do a query based on combining all the players associated with 1 Stock_ID, I get the information as it should be. That SQL is this:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblStockPlayer.Stock_ID, Max(Combination([Stock_ID],[Player_Name])) AS Players
  2. FROM tblStockPlayer
  3. GROUP BY tblStockPlayer.Stock_ID;
This gives me Stock_ID --- Player 1/Player 2/Player 3(etc) as necessary.

However when I then alter the code for a new query to combine the attributes from players, I get an incomplete combination.

For instance, let's say my subject data is:

PAttr_ID Player_ID ....PAttr_Desc
-2030941621............. 1262630099...ROY
172521253.................1262630099...400 HRs
543792050.................1262630099...1500 RBIs
970477108.................1262630099...3000 Hits

I use code:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPlayerAttributes.Player_ID, Max(Combination([Player_ID],[PAttr_Desc])) AS Attributes
  2. FROM tblPlayerAttributes
  3. GROUP BY tblPlayerAttributes.Player_ID;
The output is
Player_ID ... Attributes

The same coding that works for Players doesn't work for Attributes. Is that normal? Is there something in the module I'm missing? I've even deleted the query for Players thinking something was retained in values, but nothing works.
Jan 7 '10 #6

Post your reply

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

Similar topics

5 posts views Thread by oliver | last post: by
8 posts views Thread by Flyzone | last post: by
9 posts views Thread by =?Utf-8?B?QnJpYW4gQ29vaw==?= | last post: by
7 posts views Thread by Johny | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.