SQl query for dividing a field

[Notsogood]

I have to write a SQL statement which can divide Field1 and put the result sets in another field.In plain English I want to say, In Field1 if there is a Comma, put the letters between 2 commas, in field 2, If there is another comma put it in Field3
Field1 is an adress field and has values like 19 Stoney Gardens, Livingston, West Lothian
I want this address to be divided. I am new and am stuck now.
What is the best way to write a SQL query for this.
Many thanks

 

[Len Boorman]

You are correct , you do have a problem

Now dividing this up within Access can be a problem (Unless on of the guru's can assist) but when I have inherited a database with this problem I have resolved it using Excel. Currently you have a multi valued attribute that conflicts with 1NF

Basically create new fields for the different parts of the address.
Use a query to extract the address as it is at the moment and use Office Links to output to Excell

You can use the Find function to find position of first space and then use Left function to get first bit of address. You can repeat the Find function to get second part because the Find has a start point parameter. For the second space the start point on the find is the result of the first find +1.

Sounds complicated but not really.

Basically you can then dump the split address details bak into the new fields.

When you want an address you can have it any way you want by concatonating with spaces, commas whatever

len

 

[Notsogood]

Can you please tell me how and where to use the FIND and LEFT functions in Excel? Many thanks

 

[Len Boorman]

Attached is an example where I have taken an address and split it up using the Find, left and Mid functions


Len

 

[neileg]

There is a Split function in Access that will deal with this data. It's a while since I used it so I can't remember the syntax to work up an example, I'm afraid.

 

[Len Boorman]

Hi Neil

Looked up the Split function you referred to cos I would like to know about that one.

Search on the Help for Split gave me indicators for splitting databases or tables but not fields

Any idea what it may be listed under within Help

Cheers

len

 

[neileg]

Len, as always you need to have a code window open to get the full help files. Even then the help isn't that good. I'd do a search in these forums as well.

 

[Notsogood]

Len Thanks your example helped me, but I don't know how to make this formula work for the entire column.
Thanks again

 

[Len Boorman]

Just copy the cell down to rest of rows

L