How to split a column to multiple column

H

Honey Badger

Hello

I'm a novice with databases, and I need some help. I have an
application using ms access as data repository. I would like to rip
some information out. I linked my tables to the original database
tables. The original database has a table called users with fields
like FirstName, LastName, Address, etc...
My problem is the "Address" column it keep the full address in a
single column like this

Address
"10 Camel Street
Trumbull, MO 83613"

I need to put this into multiple columns like Street, City, State and
ZipCode. It's a line break between street and city. How do I do that
any pointers?? Is this possible?
Thanks chris
 
R

RobFMS

Honey

Parsing of Address information, like you have described, is not going to be
an easy task. I'm not sure that you will be able to automate the process. In
other words, you might have to perform some (or the majority) of the
processing by hand.

I have seen people enter address information in so many different formats.
Apartment/Suite/Route numbers are listed first before the address is listed.
In some cases, they are written like:

Ste. # 1001
Suite #1001
#1001

Is there anything consistent in the data that we can apply an assumption to?

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
H

Honey Badger

Rob

Thanks for your quick reply.
The answer to your question is sort of yes. The first line always will
be the "Street" address (variable length) followed by a line break.
Second line always starts with the City (variable length) followed by
a single comma than two letters for state and 5 numbers for zip code.

Address
"5670 Windridge view
New York, MO 52430"

Of course out of country addresses going to be different but than the
manual entry would be perfectly fine. The data entry will follow this
method in 97% of the time. Automation is certainly desired.

Thanks
chris
 
R

RobFMS

If you know that a carrriage return line feed will be present, then I
believe you will be able to use the Split() function (available in Access
2000 and up).

Use the constant "vbcrlf" as the delimiter. What you will have is an array
of 2 entries for each record. The first one will be the address part, the
second one will be the city state zipcode.

Based on the second array, you can do further processing by using the
Split() function with the delimiter as a space character: " ".

In which you will separate out the City, State and Zip Code. Don't forget to
remove the "," at the end of the City.

Does this help describe to you what the steps you will need to take?

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
H

Honey Badger

Rob

Sound exactly what I need. Only problem now is that; I have no clue
how to use it. :) I found the VBA
Split(expression[, delimiter[, limit[, compare]]])
Never did any VBA for Access. Any idea where could I find some basic
walk through on how and what to do?
Thanks a lot
chris
 
R

RobFMS

Chris

So that I have a better understanding of how best to provide assistance,
what knowledge do you have of Access? Do you know how to create queries?
Have you done any programming in Access before?

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Honey Badger said:
Rob

Sound exactly what I need. Only problem now is that; I have no clue
how to use it. :) I found the VBA
Split(expression[, delimiter[, limit[, compare]]])
Never did any VBA for Access. Any idea where could I find some basic
walk through on how and what to do?
Thanks a lot
chris

If you know that a carrriage return line feed will be present, then I
believe you will be able to use the Split() function (available in Access
2000 and up).

Use the constant "vbcrlf" as the delimiter. What you will have is an array
of 2 entries for each record. The first one will be the address part, the
second one will be the city state zipcode.

Based on the second array, you can do further processing by using the
Split() function with the delimiter as a space character: " ".

In which you will separate out the City, State and Zip Code. Don't forget to
remove the "," at the end of the City.

Does this help describe to you what the steps you will need to take?
Click to expand...
Click to expand...
 
H

Honey Badger

Rob,

I know how to create quaries but have not done any programming in
Access.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how do i merge the datas in 2 columns 3
Mailing address block: sorting 1 row to columns 1
Need If-Then-Else or Similar Construct in Test for Null Value 10
help with database 2 2
Trying to wrap my head around splitting up & combining tables 5
Company and multiple addresses DB design 1
Split address in 1 cell into 4 7
How to change address, city, state zip code into separate columns 2

Top