Separating a comma separated string into individual entries in a table

[d***@gmail.com]

Hello and thank you in advance for any help or suggestions.

I have a MS Access table that has numerous columns but the two columns I am concerned with are PartNbr and Models.

The part number is a straight forward text field, but the Models column contains from 1 to 10 model numbers that the Part Number is used in.

I have created a second table with two columns Part_Nbr and Model_Nbr.

I would like to have an entry in the new table for each Model Number and it's corresponding Part Number.

Examples:

Current Table:

PartNbr Description UnitCost Manufacture AvgUsage Models
X12345 Blue Widget $55.99 Blue Widget 32 ABC, XYZ
Z34567 Orange Roll $100.00 Some Corp 100 ABC
A30596 Red Switch $5.75 ABC Co. 1000 GRZ, ABC, XYZ, THW



New Table:

Part_Nbr Model_Nbr
X12345 ABC
X12345 XYZ
Z34567 ABC
A30596 QRZ
A30596 ABC
A30596 XYZ
A30596 THW

My question is how do I go about populating the New Table?
Thanks again!

[Ulrich Möller]

Post by d***@gmail.com
Hello and thank you in advance for any help or suggestions.
I have a MS Access table that has numerous columns but the two columns I am concerned with are PartNbr and Models.
The part number is a straight forward text field, but the Models column contains from 1 to 10 model numbers that the Part Number is used in.
I have created a second table with two columns Part_Nbr and Model_Nbr.
I would like to have an entry in the new table for each Model Number and it's corresponding Part Number.
PartNbr Description UnitCost Manufacture AvgUsage Models
X12345 Blue Widget $55.99 Blue Widget 32 ABC, XYZ
Z34567 Orange Roll $100.00 Some Corp 100 ABC
A30596 Red Switch $5.75 ABC Co. 1000 GRZ, ABC, XYZ, THW
Part_Nbr Model_Nbr
X12345 ABC
X12345 XYZ
Z34567 ABC
A30596 QRZ
A30596 ABC
A30596 XYZ
A30596 THW
My question is how do I go about populating the New Table?
Thanks again!

See here:

https://bytes.com/topic/access/answers/194750-fyi-sql-splitting-delimited-concatenated-string-into-separate-strings-rows
https://social.msdn.microsoft.com/Forums/office/en-US/1b7082b4-1389-4677-be28-bdec791b31b5/sql-query-to-split-a-table-column-into-rows?forum=accessdev

Ulrich