Import Comma-Separated Excel Data into Access Relationships
Question details
The user needs to import comma-separated Excel values into an Access database and relate them to their original records.

- Product
- Microsoft Access and Microsoft Excel
- Device & OS
- not provided
- Scenario
- An Excel column contains multiple comma-separated values for each record, and the user wants to represent those values in Access relationships.
- Observed behavior
- Importing the values into an Access multivalued field can fail and may create a structure that is difficult to maintain.
Make sure each Excel source record has a unique ID and that commas consistently separate individual values. Identify the Access table that will hold the original records and check that its ID can be matched to the Excel IDs.
Split the values and create a junction table in Access
Use separate value and junction tables to model the data as a maintainable many-to-many relationship instead of importing into a multivalued field.
The steps below use RecordID for the original record key and ValueText for each split value. Adjust the table and column names to match your database.
If the original records are not already in Access, import them separately with one row per record and set RecordID as their primary key.
In Excel, select the source data and choose Data > From Table/Range. In Power Query, select the comma-separated column, choose Split Column > By Delimiter, select a comma, and under Advanced options choose Split into Rows. Trim spaces from the resulting values using Transform > Format > Trim.
Use Home > Close & Load in Power Query to load the result to a worksheet. In Access, choose External Data > New Data Source > From File > Excel and import the split rows into a staging table named Staging, retaining RecordID and ValueText.
In Access, use Create > Table Design to create a table named ValueTable with an AutoNumber primary key named ValueID and a Short Text field named ValueText. Set ValueText to Indexed: Yes (No Duplicates). In Create > Query Design > SQL View, run: INSERT INTO ValueTable (ValueText) SELECT DISTINCT ValueText FROM Staging WHERE ValueText Is Not Null AND ValueText <> '';
Use Create > Table Design to create a table named RecordValues with RecordID and ValueID fields that match the data types of their referenced keys. Select both fields and click Primary Key to create a composite key. In SQL View, run: INSERT INTO RecordValues (RecordID, ValueID) SELECT DISTINCT S.RecordID, V.ValueID FROM Staging AS S INNER JOIN ValueTable AS V ON S.ValueText = V.ValueText WHERE S.RecordID Is Not Null;
Open Database Tools > Relationships and add the original records table, ValueTable, and RecordValues. Drag the original table's RecordID to RecordValues.RecordID, then drag ValueTable.ValueID to RecordValues.ValueID. Select Enforce Referential Integrity for each relationship and click Create.

Prepare Excel Source Data with WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface and compatibility with Microsoft Office formats, making it easy to bring existing spreadsheets into your workflow. Use WPS Spreadsheets to review source data, then use Microsoft Access to build the database relationships.

Frequently Asked Questions
Why should I use a junction table instead of an Access multivalued field?
A junction table stores one link per record and value, making a many-to-many relationship easier to query and maintain. Create it in Access with foreign keys for the original record and the separate value table.
How can I split comma-separated values into separate rows in Excel?
Select the data and choose Data > From Table/Range. In Power Query, select the column, choose Split Column > By Delimiter, and select Split into Rows under Advanced options.
What if the same value appears for several records?
Store that value once in ValueTable, then add a separate RecordValues row for each original record linked to its ValueID. The composite primary key prevents duplicate links between the same record and value.
Why won't Access enforce referential integrity?
Check in Database Tools > Relationships that the linked fields have compatible data types and that every junction-table key has a matching record in its parent table. Remove or correct unmatched keys before enabling the option.




