Break out data contained in one column

[Aaron Neunz]

I have an Access table with eight columns and 604 Records.

I need to pull out specific sections of data in the DIST column to create
several other columns.

What is the easiest way to accomplish this?

 

[Lynn Trapp]

The easiest way is to start by telling us what the data is like in the DIST
column. Without knowing that there is no way anyone could help you.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Aaron Neunz]

Yes thank you for the reply.
Now the data contained in the DIST column is this:

CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR ED07AP09

I need separate columns (it doesn't matter about the column names) like
this:
CN17, SN28, HS44, AKRO, AKRO, CCN4, AKSC, AKRO, AK01, METR, ED07, AP09

Thanks for the help

 

[Lynn Trapp]

Oh! That's going to be fun. Well, if we can assume that all the data will be
the same length with the spaces always appearing in the same places, you can
follow this process.

To get the first column do this
Left("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR ED07AP09",4)

For the Other columns,
Column2: Mid("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR
ED07AP09",5,4)
Column3: Mid("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR
ED07AP09",9,4)
Column4: Mid("CN17SN28HS44AKRO AKRO CCN4AKSCAKRO AK01METR
ED07AP09",13,4)

etc., etc. until you get all your columns. If the records have different
lengths and the spaces are in different locations, then you are going to
find it more difficult.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Aaron Neunz]

Well sure enough, all of the data will NOT be the same length with the
spaces NOT always appearing in the same places.

Thank You

 

[Lynn Trapp]

In that case you may be in a world of hurt. You can use the queries I
posted, plus a few others, to get each successive 4 character segment from
the string but, unless the spaces are consistent, you will have some
inconsistent results.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html