Splitting data in 1 column to new columns

[BarkingPig]

Hello. I'm a real dunce when it comes to Access and am hoping someone can help me. I have historical data I acquired and someone set up Access for me a few years back and now that I'm ready to review the data, am having problems with one column.

I have a column that consists of 14 numbers and are codes for about 9 things. So for example, the first # may represent one thing and the next two #'s represent another and the next two #'s again another...etc. I already have the tables created to decode these codes, but I can't figure out how to get access to then decode this data in separate columns when I run a query.

Any help is greatly appreciated. I'm going nuts here.

-Steven

 

[KKilfoil]

I am assuming that the 14 numbers are stored as a TEXT field and can be broken down with a consistant number of digits.

eg. so that the 1st character ALWAYS will become NewField1, the 2nd and 3rd character always will become NewField2, etc.

Create a select query that contains the field to be split. For discussion, let's assume it will contain the fields ID and BIGCODE.

Add another field in the query design grid with the follwing expression (using the name for the field you actually want!):
NewField1: =Mid([BIGCODE],1,1)
This will 'extract' one character, starting at position 1.

create another filed with the following expression:
NewField2: =Mid([BIGCODE],2,2)
This will extract 2 chars, starting at 2.

The third one might be:
Newfield3: =Mid([BIGCODE,4,1)
This extracts 1 character, starting at position4.

Repeat as required for your 9 fields.


Once you have the select query separating the data the way you want, you can create a new make-table query using your first query as the data source. Or if you want to permanently store the split data in an existing table (hopefully a one time effort?), you can use an update-query.

 

[BarkingPig]

Woohooo...success! Well, almost.

You have me about 75% there. I can now run a query and have new columns appear with the codes extracted in 1st, 3rd, 5th, etc, position from the original column. It's great.

<<<Once you have the select query separating the data the way you want, you can create a new make-table query using your first query as the data source. Or if you want to permanently store the split data in an existing table (hopefully a one time effort?), you can use an update-query.>>>
I'm lost. I make a new table from the query I'm using? I'll be accessing my data in various ways for some time to come. The *.mdb file I'm using is .97GB...it's big. I want to be able to modify my queries each time with new columns added and others removed whenever looking at something particular but still have these new columns come up with the "word" that "01" or "02" represents. I'm able to link other query columns to tables and have it work, but I can't with these new ones. I have no clue what to link to in the "field list" I think it's called. I hope my problem is understandable. I can tell I'm so close.

Thank you immensly!

-Steven

 

[Rich_Lovina]

Did you use the option Compact Database, which can reduce base size enormously. After that it is often necessary to keep re-making tables to get all you want in the final db. Note the option choose table or query for your new query. TableA becomes TableAA and it has all you want then you can delete tableA.
Hope these suggestions help.