472,779 Members | 2,022 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

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 3838
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,534 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

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

Similar topics

by: oliver | last post by:
hi there i'm experimanting with imaplib and came across stringts like (\HasNoChildren) "." "INBOX.Sent Items" in which the quotes are part of the string. now i try to convert this into a...
by: Andy Mee | last post by:
Hello one and all, I'm developing an Asp.NET system to take a CSV file uploaded via the web, parse it, and insert the values into an SQL database. My sticking point comes when I try to split()...
by: Richard Hollenbeck | last post by:
To prevent future apostrophe bugs and errors, isn't it just simpler to forbid an apostrophe from being entered into a text field? For example, couldn't "Alice's Restaurant" be changed to "Alices...
by: tHeRoBeRtMiTcHeLL | last post by:
Below is an earlier post to an Excel Group.. ....but I thought that there might be a way to do this in Access by importing data and then creating append and/or update query. I would most certainly...
by: Cor | last post by:
Hi Newsgroup, I have given an answer in this newsgroup about a "Replace". There came an answer on that I did not understand, so I have done some tests. I got the idea that someone said,...
by: Flyzone | last post by:
Hi, i have a problem with the split function and regexp. I have a file that i want to split using the date as token. Here a sample: ----- Mon Apr 9 22:30:18 2007 text text Mon Apr 9...
by: =?Utf-8?B?QnJpYW4gQ29vaw==?= | last post by:
I want to open a text file and format it into a specific line and then apply color to a specific location of the text and then display it in a RichTextBox after all of this is done. I can do all...
by: Gilberto | last post by:
Hello, I have a couple of forms using the code to FIND AS YOU TYPE from Allen Browne (http://allenbrowne.com/AppFindAsUType.html). It worked PERFECTLY until yesterday when i splitted the db into...
by: Johny | last post by:
I have a string of a variable length and I need to split the string in strings of 6 characters . But if the 6th character is not space then I must split the string at possition before the 6th...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

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.