Access query to split comment field into multiple records

Question

In Access 2013 I'm trying to split fields in a column into new rows but having no success with several routes I've tried. For example, Row 1 has ID # 1 with 3 codes pertaining to it separated by commas (12, 13, 14). I have been trying to run a query to separate row 1 into three rows, each having one of the codes but the same ID #.

My goal is to have the comment column split into separate records/rows with the correct corresponding ID #, etc.

I've tried several variations of the following SQL query in access with no success:

Select [1 Tracking].ID #, [1 Tracking]Codes
FROM [1 Tracking]
CROSS APPLY STRING_SPLIT([1 Tracking].Codes, ",");

Original Data on table

ID #   /   Code:
1          (12, 13, 14)
2          (13, 15)
3          (17)

(Only 3 rows) Results after running query;

ID #   /   Code:
1          (12)
1          (13)
1          (14)
2          (13)
2          (15)
3          (17)

(Now 6 rows instead of 3)

Any help would be greatly appreciated. I've only found VBA answers and have no idea how to use it. Thank you.

This just doesn't seem like something you can do readily in MS Access. You'll need VBA or some similar environment. — Gordon Linoff, Dec 20 '18 at 19:46
Unfortunately, there is no simple SQL or generic MS-Access way to do this. You will need to use VBA to accomplish this. — artemis, Dec 20 '18 at 19:57
Any VBA recommendations then? any formulas? Can struggle to get it to work lol — ThatTallGuy88, Dec 20 '18 at 20:12
Search for "ms access split column to rows" and have a look at the "Related"-Section at the right side of this page. — ComputerVersteher, Dec 20 '18 at 20:37
But why do you have such nested data stored like that in the first place? Check data sourcing. Relational databases are best used with atomic, scalar, bitwise data stored within cells of columns and rows. — Parfait, Dec 20 '18 at 21:43
you can work on [this vba code](https://stackoverflow.com/a/5539504/1118978) — Derviş Kayımbaşıoğlu, Dec 20 '18 at 21:49

Access query to split comment field into multiple records

0 Answers0