Prev: Concatenate multiple rows
Next: Count by Date
From: Dennis on 28 Apr 2010 15:56
i am looking to split up a field into multiple fields. we produce steel
tubing and the way our product show in the system is a little tricky to work
with in access. here are some examples

8 x 4 x 1/4 = 8.4.14
6 sq 3/8 = 6.38
4 x 3 x 6 ga = 4.3.6
5 round .375" = 5000.375
3.5 nps Sched 40 = 35.S40
4-1/2 x 2-1/2 x 3/16 = 412.212.316

these are just some examples. we have over 3000 products.

i would like to split this field into separate ones.
if my system shows it as
8.4.316
i would like to see in 3 different fields
1 = 8 2 = 4 3 = 316

if my system shows it as
5000.375
i would like to show it as
1 = 5000 2 = 5000 3 = 375
if i have to i can work with it showing
1 = 5000 2 = 375 3 =

i don't know if i can do this with Left middle right formulas

the field is named [SJD_SIZE]

can anyone help me with this? i know very little about SQL so if i can do
this without messing with that it would be great.
From: Jeff Boyce on 28 Apr 2010 17:54
Dennis

Just looking at that last one (412.212.316), how do you know that it isn't
"41.2 x 21.2 x 31.6"? I'm not asking to be cute, I'm asking to learn how
you'd explain to a human assistant how to parse those ... You'll need to
explain that carefully to Access.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



"Dennis" <Dennis(a)discussions.microsoft.com> wrote in message
news:10515967-CEC7-430A-A22E-DCFF7B14E273(a)microsoft.com...
>i am looking to split up a field into multiple fields. we produce steel
> tubing and the way our product show in the system is a little tricky to
> work
> with in access. here are some examples
>
> 8 x 4 x 1/4 = 8.4.14
> 6 sq 3/8 = 6.38
> 4 x 3 x 6 ga = 4.3.6
> 5 round .375" = 5000.375
> 3.5 nps Sched 40 = 35.S40
> 4-1/2 x 2-1/2 x 3/16 = 412.212.316
>
> these are just some examples. we have over 3000 products.
>
> i would like to split this field into separate ones.
> if my system shows it as
> 8.4.316
> i would like to see in 3 different fields
> 1 = 8 2 = 4 3 = 316
>
> if my system shows it as
> 5000.375
> i would like to show it as
> 1 = 5000 2 = 5000 3 = 375
> if i have to i can work with it showing
> 1 = 5000 2 = 375 3 =
>
> i don't know if i can do this with Left middle right formulas
>
> the field is named [SJD_SIZE]
>
> can anyone help me with this? i know very little about SQL so if i can do
> this without messing with that it would be great.


From: Dorian on 28 Apr 2010 18:10
You need to think about what the separate nodes (the bits between the dots)
of those fields mean. It looks like they relate to the measurements or other
specs of the item.
Even so I would not design a system where you have to derive the
measurements of the item from the code, so you will have to keep all the
specs in separate columns. A lot depends on how the data will be used. E.g
will a manager come to you and ask 'show me all the items that have a
dimension of 4 inches', or 'show me all items wider than a foot'.
You can easily split up your nodes into separate columns. Use the INSTR,
LEFT, RIGHT and MID functions (look them up in Access Help).
Is there a maximum number of nodes? Do you need to be able to create the
original strings from your newly created separate columns?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Dennis" wrote:

> i am looking to split up a field into multiple fields. we produce steel
> tubing and the way our product show in the system is a little tricky to work
> with in access. here are some examples
>
> 8 x 4 x 1/4 = 8.4.14
> 6 sq 3/8 = 6.38
> 4 x 3 x 6 ga = 4.3.6
> 5 round .375" = 5000.375
> 3.5 nps Sched 40 = 35.S40
> 4-1/2 x 2-1/2 x 3/16 = 412.212.316
>
> these are just some examples. we have over 3000 products.
>
> i would like to split this field into separate ones.
> if my system shows it as
> 8.4.316
> i would like to see in 3 different fields
> 1 = 8 2 = 4 3 = 316
>
> if my system shows it as
> 5000.375
> i would like to show it as
> 1 = 5000 2 = 5000 3 = 375
> if i have to i can work with it showing
> 1 = 5000 2 = 375 3 =
>
> i don't know if i can do this with Left middle right formulas
>
> the field is named [SJD_SIZE]
>
> can anyone help me with this? i know very little about SQL so if i can do
> this without messing with that it would be great.
 | 
Pages: 1
Prev: Concatenate multiple rows
Next: Count by Date