Normalizing semicolon delimited rows

  • Thread starter Thread starter Patrick Jackman
  • Start date Start date
P

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
 
Patrick said:
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.
Click to expand...
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
 
Thanks for the idea Bob. I'll give that a try.

Patrick.

Patrick said:
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.
Click to expand...
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
 
Back
Top