How to split a row into multiple rows

[razkowski]

Hello-



I'm trying to determine if it's possible to split 1 row into multiple rows based on a field value. Please see the attached spreadsheet. Please be forewarned, I'm no good in SQL. If you have any answer for this, please provide it in VBA or in a query design view.

Thank you

[June7]

Depends on the data in Field11. Is the data structure consistent? Will there ALWAYS be two 2-digit numbers with 'and' between them? If yes, I suggest a UNION query is simplest.

SELECT *, Left(Field11,2) AS Data FROM tablename
UN ION SELECT *, Right(Field11,2) FROM tablename;

There is no query builder or wizard for UNION, must type into SQL View of query designer.

Otherwise, will probably involve VBA writing records to a table.

[razkowski]

No, the data structure will change. Sometimes the 2 numbers are separated by "and", sometimes "&", sometimes "/" etc. This is vendor provided data and I cannot change that. The data will always start with a 2 digit number and end with a 2 digit number, that's the only thing I can count on.

[June7]

Okay, my suggestion doesn't really care what falls in the middle, as long as each record has a value with the 2-digit numbers on each side, should work.