Data from String

[SteveE]

I have the following Codes in a single string, “ ,WB3,W10,W01,W65,W31 “ each seperate code is stored in another table with its meaning i.e. WB3 = Re-Box etc.

What is the best way to pull these meanings in from the assioiated table.
I can do this for a single code but I am struggleing when there are more.
I am thinking an Instr function but not sure of the syntax etc

The query result I am looking for is

Order StringCodes Code Descr
111111 ,WB3,W10,W01,W65,W31 WB3 Re-Box etc.
111111 ,WB3,W10,W01,W65,W31 W10 Pre-10 am
111111 ,WB3,W10,W01,W65,W31 W01 Helper
111111 ,WB3,W10,W01,W65,W31 W65 Remove Packaging
111111 ,WB3,W10,W01,W65,W31 W31 Stair Walker
any advise appriciated
Steve

 

[jdraw]

I have the following Codes in a single string, “ ,WB3,W10,W01,W65,W31 “ each seperate code is stored in another table with its meaning i.e. WB3 = Re-Box etc.

What is the best way to pull these meanings in from the assioiated table.
I can do this for a single code but I am struggleing when there are more.
I am thinking an Instr function but not sure of the syntax etc

The query result I am looking for is

Order StringCodes Code Descr
111111 ,WB3,W10,W01,W65,W31 WB3 Re-Box etc.
111111 ,WB3,W10,W01,W65,W31 W10 Pre-10 am
111111 ,WB3,W10,W01,W65,W31 W01 Helper
111111 ,WB3,W10,W01,W65,W31 W65 Remove Packaging
111111 ,WB3,W10,W01,W65,W31 W31 Stair Walker
any advise appriciated
Steve

Are you telling us that in the first record, the
WB3,W10,W01,W65,W31 each represent a different "thing"?

This is not normalized.
You would have

Order Code CodeDesc
111111 WB3 Re-Box etc.
111111 W10 Pre-10 am
etc.
etc.


You could parse the strings into a temp table, but I'd be looking
to get Codes into individual records associated with Order.

 

[SteveE]

Yes the order ie 111111 would have several different service codes agaiinst it
the way the file is delivered to us these are in a single field string with seperated by a commer.
I have managed to resolve this using an instr function Expr1: InStr([DELPLUSCODES],[WCode])which gives me a 0 if the code in not in the services table filtering by >0 gives me each code and description .

ORDER NO DELPLUSCODES WCode WCodeDesc
111111 ,W10,WA8,W65,W31 W31 PALLETISED DELIVERY.
111111 ,W10,WA8,W65,W31 W10 EURO PALLET.
111111 ,W10,WA8,W65,W31 WA8 MAX HEIGHT 2.1M
111111 ,W10,WA8,W65,W31 W65 PALLET HEIGHT RESTRICTIONS.

thank for you time.
regards
Steve

 

[DCrake]

Looking at the make up of your incoming file it appears that the last Wcode is the code relating to the narrative that follows it. There are 5 columns or codes the first 4 are unique but the 5th is a repeat of on of the wcodes in the first 4.

So you need to use the Mid() function to extract the final wcode and use this to store in your table along with the order no.

111111 ,W10,WA8,W65,W31 W31 PALLETISED DELIVERY.
111111 ,W10,WA8,W65,W31 W10 EURO PALLET.
111111 ,W10,WA8,W65,W31 WA8 MAX HEIGHT 2.1M
111111 ,W10,WA8,W65,W31 W65 PALLET HEIGHT RESTRICTIONS.

This issue here though is how do we know how many wcodes there are. There is two ways to do this. The easiest way is to use the InStrRev() function

First we find the last comma in the string

X = InStrRev(AnyString,",")

Now if we know that all Wcodes are 3 digits long then we can add 5 to this value

X = InStrRev(AnyString,",")+5

Finally we can use the mid() string to extract the last 3 digit wcode

W = Mid(AnyString,InStrRev(AnyString,",")+5,3)

 

[SteveE]

Sorry my exanmple wasent clear there are only 4 codes in the string the additional one you can see is from the services table along with the description
111111 ,W10,WA8,W65,W31 W31 PALLETISED DELIVERY.
111111 ,W10,WA8,W65,W31 W10 EURO PALLET.
111111 ,W10,WA8,W65,W31 WA8 MAX HEIGHT 2.1M
111111 ,W10,WA8,W65,W31 W65 PALLET HEIGHT RESTRICTIONS.

regards
Steve

 

[gemma-the-husky]

you shouldnot try and use unnormalised data form a txt file/csv file directly in access

you really need a process to deconstruct/normalize this data and store the resulting details in access tables

this can be quite a long complex process, but can be automated, and once automated will be done quickly for each new file. You really have to understand how to construct normalised tables, though, to be able to make a start on this.

 

[DCrake]

Why did you not say that in the first place?

What is the actual object of the exercise?
What are you trying to achive?

David

 

[SteveE]

the object was to extract from a single string composed of x number of codes the descreption of each code. I was stuck but I have been able now to successfully do it using my instr function as explained eairler.
Many thanks for you offers of help though appriciated.

regards

Steve