Comma delimited list to multiple lookup values in Access 2010

December 14, 2012 2:14 AM

Is there an easy way of turning a comma-separated list into multiple lookup values in access 2010?

A colleague has an Excel table, the last column of which contains multiple values, comma delineated. These represent a one-to-many relationship with data in another table.

We're trying to move it across to Access 2010. The relationship is set up, but there's simply too much information in the table to manually enter each item. Obviously, each value in the comma separated column needs to become a separate entry in the link table.

Is there a way of getting the info across?

posted by monkey closet to Technology (1 answer total)

Explode the value column with Text To Columns, copy the key column as many times as required between the new multiple value columns, sort everything in one big list, remove blank values and Robert is your mother's brother.
posted by Dr Dracator at 2:29 AM on December 14, 2012 [2 favorites]