Split name, city and state

[Please_help_me]

PLease help me to split, name, city and state.

I have a database with couple of column. 1 of the colum (need to be split) contains:
John, Edison, NJ

I need to split Edison to the next column also NJ to the next column after the edison.

Please kindly help me, since i dont know anything about access.
Thank you for the help.

 

[mike-strawberry]

Hi,

Since this is a week ago since you posted I don't know if you got help already so will make this breif for now then I will (or maybe someone else will) get back to you if you still need it.

You need to add two new fields (columns) to the the same table as the one with the address column.

Then you can use update queries so that the existing column and the two new ones end up having the FirstName SecondName and State. If the table to be split only has say 200 addresses and has a lot of the same states in you can query your table with the new columns added then filter by state and manually retype the state into your new state column in the top record then go down using the two keys ctrl+' to copy. Using update queries is otherwise better. If your address table is really big then you will need a bit of code that recognises comma and or spaces between data in order to deduce where each of the 3 words start and finish then it will place them in the columns. One bit of code can probably update it all in one go.

It may be a day before I get back to this forum.

Bye for now. - Mike

 

[Please_help_me]

Hi Mike,

Thank you for reply my post.

Kindly please see the attachment.
what i'm trying to do is to split name from city and state.
Column Profile contains: name, city and state.

What i'm trying to do is to
- Move the city name from profile column into City Column.
- Move the state name from profile column into state column.

Please help.
Thank you

 

[mike-strawberry]

I now have the complete solution to what you asked and have tested it.
PART 1 of 2: check state.

Due to the length of my solution I am posting it in separate stage chunks.

• This solution assumes that all addresses currently stored have two letter States at the end of the current profile field, and that no foreign or other formats are present. The next step verifies all is in order. The verification that follows will not be enough to verify if the US two letter state format is not applied to most addresses. I doubt if there are any two letter cities but if there is an input error with both no state and two letters for the city then the city is going to go into the state field unless you correct it. If you think that is possible then I can give you a further test in another post if you ask for it which will check the last two letters match a state. But you could decide that if there is such an error then the result will still just be an error which may just get sorted when spotted some time.
• If the addresses are all meant to have two letter states and there is a small number of errors then you can make corrections using the CheckState query. If you wish to get on with doing this split but need someone to work on inserting the correct States after (I’m thinking for all I know you may have 2 million records) then where state has not been filled in put in two letters such as or XX (or two ** symbols if you prefer) so that the field has a nominal two character state part. You can then after the split updates create a form that filters for XX or ** which will be the list of profiles that need to be updated. If the number of records is very large I can give you a solution which will make the updating task easier.
• If you have US states UK counties and all sorts then let me know first and I’ll change the state split part of this solution.

• If you don’t know how to do any of this let me know and I’ll help some more.

CheckState query. The table that contains the data I believe is called ‘details’. Create a new query in design view and add the details table. Drag the profile field to the grid column on the left. In the next column on the right, in the top row type the following two words To Edit: the colon after is important. After the colon type (Len([Profile]))-(InStrRev([Profile]," ")) check the brackets and all carefully. When you think its ok click anywhere into the next empty column, that will instruct access to save what you have typed and it will look for errors for you and may give you a message meaning check the brackets and everything again. Then add into the To Edit column against criteria not equal two, enter is as <>2 Then press the run button at the top of the screen to run the query. If you get a small blank line result it means everything is fine – every single record has a two letter state ending. If not you will see the records that need correcting.
Let me know how this goes - mike

 

[mike-strawberry]

PART 2 of 2.

Please make at least one back-up copy of your table before you start incase you accidently run a query twice which could be disastrous.

In my query designs you will see that I accidently used the table name Customers instead of detail so in yours it will read details.

Not sure when you said you don't know Access how much so incase you need it- to create an update query (to get the Update To line you see in the attachments), when you open a new query in design view, right click in the blank space part and select query type then update query. When you run the query it notifies you how many records you are updating, press ok and it does it but it may look like nothing happened - do not run the query again as it may have very bad result which may be difficult or impossible to undo. If you check the table you will see what it has done. If it is ok close the query without saving. If you have already saved it do not click on it or that will run it again. Just right click it and delete it.

First create and run a query that will put the state into the state field.(Insert State Query)

Then create and run a query that will clean up the profile field to remove the space and comma left at the end.(Update Profile ex-State).

Then create and run the final query that finds where the now one and only comma seperator is and uses that marker to recognise and place the City in City field and name over-write into Profile field. The attachments probably do better than me describing them.(Update City and Profile fields).

I was pleased when it worked, hope you are.

-- Mike

 

[Please_help_me]

PART 2 of 2.

Please make at least one back-up copy of your table before you start incase you accidently run a query twice which could be disastrous.

In my query designs you will see that I accidently used the table name Customers instead of detail so in yours it will read details.

Not sure when you said you don't know Access how much so incase you need it- to create an update query (to get the Update To line you see in the attachments), when you open a new query in design view, right click in the blank space part and select query type then update query. When you run the query it notifies you how many records you are updating, press ok and it does it but it may look like nothing happened - do not run the query again as it may have very bad result which may be difficult or impossible to undo. If you check the table you will see what it has done. If it is ok close the query without saving. If you have already saved it do not click on it or that will run it again. Just right click it and delete it.

First create and run a query that will put the state into the state field.(Insert State Query)

Then create and run a query that will clean up the profile field to remove the space and comma left at the end.(Update Profile ex-State).

Then create and run the final query that finds where the now one and only comma seperator is and uses that marker to recognise and place the City in City field and name over-write into Profile field. The attachments probably do better than me describing them.(Update City and Profile fields).

I was pleased when it worked, hope you are.

-- Mike

Mike.
Thank you for the answer, will let you know soon.

 

[Please_help_me]

Mike,

I tried to create a query that you show me, but i dont see "update to" field on my query column, (i have "sort" and "show") do i have to change or add something?

Please see the picture attached.
Thank you

 

[Please_help_me]

Mike,

You are Genius !!!!
Thank you so very very much for your help.

 

[mike-strawberry]

You're welcome. It really pleased me that I gave you an answer that worked for you. Cheers.