Expert guide

Access Multivalued Fields: Problems and How to Replace Them

A convenient shortcut that hides a relationship and blocks migrations later

Free Cost Audit

Call us at +1 (385) 386-3860

Short answer: multivalued fields store several choices per record in a hidden system table. They are quick to set up but complicate queries and VBA, cannot move to SQL Server, and cannot hold details about each choice. Replace each one with a junction table (parent ID + lookup ID), copy existing selections into it with a query on Field.Value, switch the form to a subform or list box, update queries and code, then delete the old field. This is a standard part of our Access database design work.

Multivalued fields appeared in Access 2007 to make many-to-many choices easy for beginners: tick several categories, skills or tags in a drop-down list. For a personal database they can be fine. In a business database that grows, gets reported on, or moves to SQL Server, they become one of the first things a developer has to undo.

What Goes Wrong

AreaProblem
QueriesField.Value returns one row per choice, multiplying rows in joins and totals unexpectedly
CriteriaFiltering on 'has skill A and skill B' is awkward compared with a junction table
VBARequires Recordset2 and child recordsets; ordinary code breaks
SQL Server / other databasesNo equivalent type; migration tools cannot move them as-is
ExportsValues export as delimited text, losing structure
Extra detailsCannot store level, date or notes per selection
VisibilityThe relationship lives in hidden system tables

Find Every Multivalued Field

Public Sub ListMultiValuedFields()
    Dim td As DAO.TableDef, fld As DAO.Field
    For Each td In CurrentDb.TableDefs
        If Left$(td.Name, 4) <> "MSys" Then
            For Each fld In td.Fields
                If fld.IsComplex Then
                    Debug.Print td.Name & "." & fld.Name & _
                        IIf(fld.Type = dbAttachment, "  (attachment)", "  (multivalued)")
                End If
            Next fld
        End If
    Next td
End Sub

IsComplex is True for multivalued and attachment fields. Attachments have their own replacement path, covered in the Access 2 GB limit guide.

Convert to a Junction Table, Step by Step

Example: tblEmployees has a multivalued field Skills that looks up tblSkills (SkillID, SkillName).

  • 1. Back up

    Copy the back end. Close all users out.

  • 2. Create the junction table

    tblEmployeeSkills: EmployeeID (Number, FK), SkillID (Number, FK), plus any extra fields such as Level and CertifiedOn. Add a unique index on EmployeeID + SkillID and relationships with referential integrity.

  • 3. Copy existing selections

    Run the append query below.

  • 4. Check counts

    Rows in the junction table should equal the number of selections in the multivalued field.

  • 5. Update the form

    Replace the multivalued combo with a subform bound to tblEmployeeSkills, or a multi-select list box with code.

  • 6. Update queries, reports and VBA

    Search exported code and SQL for 'Skills'.

  • 7. Remove the old field

    Delete the multivalued field once everything uses the junction table, then compact.

INSERT INTO tblEmployeeSkills (EmployeeID, SkillID)
SELECT e.EmployeeID, e.Skills.Value
FROM tblEmployees AS e
WHERE e.Skills.Value Is Not Null;

Queries Become Simpler

Employees with BOTH forklift and first aid:
SELECT e.EmployeeID, e.Name
FROM tblEmployees AS e
WHERE e.EmployeeID In (SELECT EmployeeID FROM tblEmployeeSkills WHERE SkillID = 3)
  AND e.EmployeeID In (SELECT EmployeeID FROM tblEmployeeSkills WHERE SkillID = 7);

Skill counts:
SELECT s.SkillName, Count(es.EmployeeID) AS Employees
FROM tblSkills AS s LEFT JOIN tblEmployeeSkills AS es ON s.SkillID = es.SkillID
GROUP BY s.SkillName;

A Form That Is Just as Easy to Use

  • Continuous subform on the employee form: one row per skill, combo box for SkillID, extra columns for level and dates.

  • Or a multi-select list box of all skills, with code that writes ticked items to the junction table on save.

  • Users keep the convenience; the database keeps a proper structure.

Do This Before a SQL Server Migration

Converting multivalued fields first keeps the migration straightforward and lets you test the new structure while still on Access. It belongs on the same pre-migration list as the checks in data quality checks for Access and the planning in AI-assisted Access to SQL Server migration. Attachment fields should move to file paths at the same time; see the Access 2 GB limit.

FAQ

Multivalued fields FAQ

Problems, queries and conversion

A multivalued field is a lookup field that lets one record store several selected values, such as several skills on an employee or several categories on a product, shown as a check-box list. Behind the scenes Access stores the values in a hidden system table, not in the field itself.

They hide a many-to-many relationship that you cannot see or manage directly, make queries and VBA more complicated, are not supported by SQL Server or most other databases, complicate exports and upgrades, and limit what you can store about each selection.

Not directly. SQL Server has no equivalent field type. You must convert each multivalued field into a proper junction table before or during the migration, and update forms, queries and code that used it.

Use the .Value suffix, for example SELECT Name, Skills.Value FROM tblEmployees, which returns one row per selected value. Criteria on Skills.Value filter by individual selections. It works, but behaves differently from normal fields, which surprises users and breaks some joins and grouping.

A junction table: for employees and skills, tblEmployeeSkills with EmployeeID and SkillID (and optional extra fields such as level or certified date), with a subform or a list box on the employee form for selection. It is standard, portable and flexible.