Split a Text String and Append to other Table.

[HSXWillH]

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.

Private Sub Form_Close()
 
Dim Player_Hdr As String  
 
Player_Hdr = "INSERT INTO P_Hdr ( Player_Name ) SELECT Stock_Catalog.Player_Name FROM Stock_Catalog WHERE (((Stock_Catalog.Player_Name) Is Not Null))"
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL Player_Hdr
    DoCmd.SetWarnings True
 
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.

[mshmyob]

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.

cheers,

[HSXWillH]

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?

[mshmyob]

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.

cheers,

[NeoPa]

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.

[HSXWillH]

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.

Public Function Combination(strID As String, strAttribute As String) As String
 
    Static strLastID As String
    Static strAttributes As String
 
    If strID = strLastID Then
        strAttributes = strAttributes & "/" & strAttribute
    Else
        strLastID = strID
        strAttributes = strAttribute
    End If
    Combination = strAttributes
 
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:

SELECT tblStockPlayer.Stock_ID, Max(Combination([Stock_ID],[Player_Name])) AS Players
FROM tblStockPlayer
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
-474734113................1262630099...MVP(x2)
172521253.................1262630099...400 HRs
472121878.................1262630099...HOF
543792050.................1262630099...1500 RBIs
970477108.................1262630099...3000 Hits
1262789769................1262630099...MVP

I use code:

SELECT tblPlayerAttributes.Player_ID, Max(Combination([Player_ID],[PAttr_Desc])) AS Attributes
FROM tblPlayerAttributes
GROUP BY tblPlayerAttributes.Player_ID;

The output is
Player_ID ... Attributes
1262630099...ROY

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.