Parse Record with Delimited Field Into Multiple Records

[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

 

[Roger Carlson]

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!