Question about data in a field

  • Thread starter Thread starter Carol
  • Start date Start date
C

Carol

Hello -

I have a field in a table that is titled "Description"
and includes a textual description of certain
categories. At the end of the description there are
different codes that I would like to have in a separate
field. Is it possible to "split" the field so that the
codes can be in a separate field? Or, is it possible to
somehow extract those codes and put them into a separate
field?

I have about 15,000 descriptions, so it's not possible to
do it manually.

Any help would be greatly appreciated!

Thanks!
 
Hello -

I have a field in a table that is titled "Description"
and includes a textual description of certain
categories. At the end of the description there are
different codes that I would like to have in a separate
field. Is it possible to "split" the field so that the
codes can be in a separate field? Or, is it possible to
somehow extract those codes and put them into a separate
field?

I have about 15,000 descriptions, so it's not possible to
do it manually.

Any help would be greatly appreciated!

Thanks!
Click to expand...

It's possible. What version of Access are you using? Recent versions
have a function InStrRev() which can find (for example) the *last*
blank in a text string, just as InStr() can find the *first*.

And, how is the code delimited? Could you post a couple of examples?
 
I'm using Access 2002...I'm not that savvy with it yet
though and don't know any programming language (VB), so
if you could explain how to do this without using a
programming language, it would be great!!

For an example, at the end of the text, the codes read
something like EOE.103.122. I need to take those codes
and put them into their own field, but they need to
correspond with the text that they belong to...thanks
again for your help!
 
Hi Carol,

If you can depend on the period as a delimiter, then you can create the new fields in your
table and run an update query to update the values appropriately. You would use functions
such as Left, InStr, etc. to split the source field at the appropriate locations.

Perhaps an easier method is to export your table to Excel (assuming you do not have more
than 65,536 rows) and then use the menu option Data > Text to columns... This method has
the ability to treat consecutive delimiters as one, which can be very useful at times.

You can accomplish this export fairly easily in several ways. My favorite method is to
select the table or query in the database window, and then drag it to the Windows taskbar
and drop it onto a previously opened Excel spreadsheet. The spreadsheet should open,
allowing you to drop it in whatever cell you want to.

After splitting the data in Excel, you can import it back into Access.

Tom
______________________________________


I'm using Access 2002...I'm not that savvy with it yet
though and don't know any programming language (VB), so
if you could explain how to do this without using a
programming language, it would be great!!

For an example, at the end of the text, the codes read
something like EOE.103.122. I need to take those codes
and put them into their own field, but they need to
correspond with the text that they belong to...thanks
again for your help!
 
I'm using Access 2002...I'm not that savvy with it yet
though and don't know any programming language (VB), so
if you could explain how to do this without using a
programming language, it would be great!!

For an example, at the end of the text, the codes read
something like EOE.103.122. I need to take those codes
and put them into their own field, but they need to
correspond with the text that they belong to...thanks
again for your help!
Click to expand...

Tom's suggestion may be the best bet - you'll need SOME programming to
get this done, otherwise, since there is an arbitrary number of codes.
A simple query could find the *last* code:

LastCode: Mid([yourfield], InStrRev([yourfield], ".") + 1)

but if there are two, or three, or four, or five codes in various
records, it's going to require some more work.

You'll also need to think about what you'll do with these codes once
you've extracted them - I'd suggest a table of Codes and their text,
and a third table with the Primary Key of this table and the code to
link your table to the table of codes.
 
Carol said:
I'm using Access 2002...I'm not that savvy with it yet
though and don't know any programming language (VB), so
if you could explain how to do this without using a
programming language, it would be great!!

For an example, at the end of the text, the codes read
something like EOE.103.122. I need to take those codes
and put them into their own field, but they need to
correspond with the text that they belong to...thanks
again for your help!
Click to expand...
 
Back
Top