A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

split up one field into multiple fields



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 08:56 PM posted to microsoft.public.access.queries
Dennis
external usenet poster
 
Posts: 1,222
Default split up one field into multiple fields

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.
  #2  
Old April 28th, 2010, 10:54 PM posted to microsoft.public.access.queries
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default split up one field into multiple fields

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" wrote in message
...
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.



  #3  
Old April 28th, 2010, 11:10 PM posted to microsoft.public.access.queries
Dorian
external usenet poster
 
Posts: 542
Default split up one field into multiple fields

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.