Btukfyl

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.

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.

With an unknown number of comma-delimited strings, it is likely easiest to iterate over the table using a recordset, use the Split function to separate the items, and populate another recordset, e.g.:

Function ConvertTable()

    Dim strOldTable As String: strOldTable = "Table1"

    Dim strNewTable As String: strNewTable = strOldTable & "_new"



    Dim dbs As DAO.Database

    Dim rst1 As DAO.Recordset

    Dim rst2 As DAO.Recordset

    Dim lngCID As Long

    Dim itm



    Set dbs = CurrentDb



    DoCmd.CopyObject , strNewTable, acTablem, strOldTable

    dbs.Execute "delete from [" & strNewTable & "]"



    Set rst1 = dbs.OpenRecordset(strOldTable)

    Set rst2 = dbs.OpenRecordset(strNewTable)

    If Not rst1.EOF Then

        rst1.MoveFirst

        Do Until rst1.EOF

            lngCID = rst1!Contact_ID

            For Each itm In Split(rst1!Field1, ",")

                If Trim(itm) <> "*" Then

                    rst2.AddNew

                    rst2!Contact_ID = lngCID

                    rst2!Field1 = Trim(itm)

                    rst2.Update

                End If

            Next itm

            rst1.MoveNext

        Loop

    End If

    rst1.Close

    rst2.Close

    Set rst1 = Nothing

    Set rst2 = Nothing

    Set dbs = Nothing

End Function