Normalizing semicolon delimited rows

[Patrick Jackman]

Is it possible to transform data in a semicolon delimited field into
individual rows by using a query in Access? I can write VBA to do this but
would prefer to use a query if possible.

Each entry in the field is 6 characters in length separated by a semicolon.

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774

 

[Bob Barrows]

Is it possible to transform data in a semicolon delimited field into
individual rows by using a query in Access? I can write VBA to do
this but would prefer to use a query if possible.

Each entry in the field is 6 characters in length separated by a
semicolon.

I suppose you could use a union query if there is a set number of data
pieces:
select left(fieldname,6) as newfield from tablename
union
select mid(fieldname,8,6) from tablename
union
select mid(fieldname,15,6) from tablename
....
union
select right(fieldname,6) from tablename

 

[Patrick Jackman]

Thanks for the idea Bob. I'll give that a try.

Patrick.

Is it possible to transform data in a semicolon delimited field into
individual rows by using a query in Access? I can write VBA to do
this but would prefer to use a query if possible.

Each entry in the field is 6 characters in length separated by a
semicolon.

I suppose you could use a union query if there is a set number of data
pieces:
select left(fieldname,6) as newfield from tablename
union
select mid(fieldname,8,6) from tablename
union
select mid(fieldname,15,6) from tablename
....
union
select right(fieldname,6) from tablename