Multiple Rows from one comma delimited field

[MiscMe]

I have a table that includes a Name field, and another field which indicates their fields of interest. The items in this field are comma delimited. For example:

Name Interest
Joe Hiking, Biking, Jogging
Sue Biking, Walking
Tom Hiking, Jogging

Ultimately I need to create a report that will break down like this:

HIKING


Joe
Tom

BIKING
Joe
Sue

JOGGING
Joe
Tom

I'm guessing I first need a query that's going to create multiple records per NAME based on their various interests, but I can't figure out how to do that.

Thanks for any help.

[kd2017]

I'd say your first course of action would be to make a new table that holds all of the interest values. Then you would create a third table to associate the interests to the names, YouTube "access many-to-many relationship" and you'll find plenty of videos to walk you through the concept and process. After that generating your report would be trivial.

BTW you probably don't want to name any field or object "name".

[orange]

You are dealing with a 3 table set up( and possibly more) that allows 1 Person to have more than 1 Interest and allows the same Interest to be had by multiple Person(s).

TblPerson----->tblPersonHasInterest<-----tblInterest


See this info on database design

[Micron]

I suppose the example doesn't accurately reflect the situation. If the data set is limited (quantity wise) because this is a new db under construction then sure, the design is faulty and needs to be repaired. If the case is really that you have hundreds/thousands of records where the compound data was designed this way, you'll need a custom function that a query can call for each field. However, if the number of comma separated values varies, you'd have to be content with Nulls in some fields in some records.