Access convert multi-valued field table

Question

I've taken over a database that has a table with mutli-valued fields e.g

I'm trying to convert this into a single-valued field table e.g.

The number in both tables is just an ID that refers to a person named Contact_ID.

I've tried doing this in both Access using Crosstab queries and Excel with Transposing however I can't produce a new table with multiple examples of the same Contact_ID in one column and unique keywords in the second.

Appreciate any suggestions on the best way to do this.

Answer 1

The values in your multivalue field will be coming from a table in your database.

To return the individual items you need to add .Value to your query field.

For example,

If Table1 contains ID as an AutoNumber and MyLookUpItems as individual items.

| MyLookUpID | MyLookUpField |
|------------|---------------|
| 1          | Alcohol       |
| 2          | Smoking       |
| 3          | diet          |
| 4          | Nutrition     |

Table2 contains MyMultiValueField showing multiple values.

| MyMultiValueField |
|-------------------|
| Smoking, diet     |
| Alcohol, diet     |

To return the individual items you'd use:

SELECT MyMultiValueField.Value
FROM   Table2  

This would return:

| MyMultiValueField.Value |
|-------------------------|
| diet                    |
| Smoking                 |
| Alcohol                 |
| diet                    |

You can then link back to the original ID:

SELECT MyLookUpID, MyLookUpField
FROM Table2 INNER JOIN Table1 ON Table2.MyMultiValueField.Value = Table1.MyLookUpID  

which would return:

| MyLookUpID | MyLookUpField |
|------------|---------------|
| 1          | Alcohol       |
| 2          | Smoking       |
| 3          | diet          |  

If you're not sure which is the source for the multivalue fields open the table in design view and look at the Lookup tab for the field.
The Row Source will look something like:

SELECT [Table1].[MyLookUpID], [Table1].[MyLookUpField] FROM Table1 ORDER BY [MyLookUpField]; 

Showing that Table1 is the source.

Edit:
Now, having written all that.... just look at the design for the table and the table in the Row Source is the single-value field table you were looking for in your question.