Display partial data in query

[Jen]

I have a fields in a two tables that contain mixed information
Table 1 Field A Table 2 Field A
ABC123 ABC123/1234
DE126 DE126/1245
There in not always the same number of letters and numbers
I want to split the field so to speak in a query ie.
Table 1 Field A Field B Field C
ABC123 ABC 123
DE126 DE 126

Table 2 Field A Field B Field C Field D
ABC123 ABC 123 1234
DE126 DE 126 1245

How can I do this?

 

[John Vinson]

I have a fields in a two tables that contain mixed information
Table 1 Field A Table 2 Field A
ABC123 ABC123/1234
DE126 DE126/1245

You're paying the price for violating the very basic principle that
data should be "atomic": storing two disparate pieces of information
in one fiedl is A Bad Idea, for the very reason you're experiencing!

Any chance you could normalize this data, so that "ABC" is stored in
one field, "123" in a second, and "1234" in a third? It's very easy to
concatenate them but much harder to pull them apart. Extracting the
text after and before the slash isn't hard:

FieldD: Mid([FieldA], InStr([FieldA], "/") + 1)

but breaking up the ABC123 will require some VBA code. And it will
have to deal with (erroneous?) entries such as A3B123 - what would be
the correct results for THAT?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Jen]

Hi John,
We do not even want to discus why the data is like that, it came from a
different database and that is the only way it will export to excel. I would
throttle the designer myself if I could. As it is I am importing the data
from excel into access and for obvious reasons need to split it. The up side
is the start of the string is always letters, file code, then numbers, file
number, then a “/†then another number, sequence. It is never different from
that. The difference file codes range from 2 to 4 letters, file numbers and
sequences obviously grow numerically. I am not adding new data to this
database just have to adapt the existing.

Help please!
Jen

I have a fields in a two tables that contain mixed information
Table 1 Field A Table 2 Field A
ABC123 ABC123/1234
DE126 DE126/1245

You're paying the price for violating the very basic principle that
data should be "atomic": storing two disparate pieces of information
in one fiedl is A Bad Idea, for the very reason you're experiencing!

Any chance you could normalize this data, so that "ABC" is stored in
one field, "123" in a second, and "1234" in a third? It's very easy to
concatenate them but much harder to pull them apart. Extracting the
text after and before the slash isn't hard:

FieldD: Mid([FieldA], InStr([FieldA], "/") + 1)

but breaking up the ABC123 will require some VBA code. And it will
have to deal with (erroneous?) entries such as A3B123 - what would be
the correct results for THAT?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps