Access convert multi-valued field table

Question

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

Current multi valued field example

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

Desired single value field table

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.

So you want each comma separated entry on its own row with the same Id number as the original row, right? — Nick, Nov 27 '18 at 12:29
Yes precisely. An option I'm looking into involves a new table with a separate FieldName (Field 1, Field 2 etc) for each keyword and then creating a query from that to show all keywords for each ID and copying that into a new table. It's not particularly elegant and I'm still holding out for a simpler solution. — TonyL, Nov 27 '18 at 12:58

Darren Bartrup-Cook · Accepted Answer · 2018-11-27T13:55:50.430

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.

score 0 · Answer 2 · answered Nov 27 '18 at 13:28

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

Access convert multi-valued field table

2 Answers2