Parse Record with Delimited Field Into Multiple Records

  • Thread starter Thread starter Henry
  • Start date Start date
H

Henry

All,

I need to parse a single record in a table with a field that has multiple
delimited values into multiple records. I would like all the new records to
have identical values in all other fields but have only a single value from
the field with the delimited values. The number of values in the delimited
field is variable with a max of 32. I assume I am looking at a UNION query
using some function that will parse out the delimited values. I was able to
find something in MySQL that talks about a function called SUBSTRING_INDEX
that seems to do what I am looking for. Does anyone know a similar function
in Access. Thanks

Example:

Field1 Field2 Field3
Andy Hockey, Football, Baseball $20

Field1 Field2 Field3
Andy Hockey $20
Andy Football $20
Andy Baseball $20



Henry
 
No, no, no! You do NOT want to create a single table with all information
the same except for the parsed field. You want to create a SEPARATE table
with Field3 and a foreign key which you can then relate back to the main
table. To do otherwise will introduce data integrity issues that will make
your database unreliable.

On my website is a small sample database called "ParseStuff.mdb" that shows
you how to parse your data into separate field. The same process will work
with a single table as you envision, but I would encourage you to use the
two related table approach. In the long run it will save you multiple
headaches!
 
Back
Top