Database Storage - Inherited data

[Guest]

The community group I am involved with wants to convert an EXCEL worksheet
into a database that could be used as a mailing list. The EXCEL worksheet
contains 10,000+ addresses that are in the format: 1015 W Armitage (one
field). In order to sort the addresses in ACCESS, I need to convert the one
field EXCEL address into three fields in ACCESS. For example, 1015 W
Armitage, as shown in EXCEL, needs to be converted to the following ACCESS
format:

field 1: 1015
field 2: W
Field 3: Armitage

Is there a way to delete the W Armitage and leave the 1015? That would
create field 1. Then I could manually enter "W" for field 2, and use an
update query to add "Armitage" for field 3.
Thank you.

 

[Joseph Meehan]

The community group I am involved with wants to convert an EXCEL
worksheet into a database that could be used as a mailing list. The
EXCEL worksheet contains 10,000+ addresses that are in the format:
1015 W Armitage (one field). In order to sort the addresses in
ACCESS, I need to convert the one field EXCEL address into three
fields in ACCESS. For example, 1015 W Armitage, as shown in EXCEL,
needs to be converted to the following ACCESS format:

field 1: 1015
field 2: W
Field 3: Armitage

Is there a way to delete the W Armitage and leave the 1015? That
would create field 1. Then I could manually enter "W" for field 2,
and use an update query to add "Armitage" for field 3.
Thank you.

I think you want to import the data as is. Create new fields to that
database then use the left right and mid functions to populate them. I have
not worked with this, so I will leave it up to you to work out the best
functions to use or maybe someone who has will offer some suggestions.

I would not be surprised if someone has some good code to do this
reasonable accurately. Note that it is not easy to do this as there are so
many variations with double name streets like maybe South White Oak Drive vs
S White Oak Dr. or even S W Oak Dr.

Good Luck

 

[Craig Hornish]

Hi,
I also saw in your other post that you aslo wanted to split the names.
Like Joseph I would import the everthing into Access an work with it from
there. I would use an udate query on the table that had the fields
FullName, FirstName, LastName
This is quick and dirty but it will give you a starting point.
For the update to row in First name you can put
"Left([FullName],InStr(1,[FullName]," ")-1)" and for the LastName you can
use "Mid([FullName],InStrRev([FullName]," ")+1)"
This will ignore any initials but it also will not catch Billy Bob
Thorton or Padilla de Pérez. The Bob and de will be dropped. Human
intervention is needed for this situation.

Now onto the addressess. The first question that comes to mind is WHY?
If this is really only a mailing list then there is really no need to split
and address field down to this level. The only reason I can think of to
split out the name of the street is to target a particular area in a city.
I also can't believe that all the addressess will be perfectly formated with
a number a letter and the name of the street.

Hope the function help you to start.

Craig Hornish
(e-mail address removed) - so I can delete it when it becomes a spam magnet

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"

 

[Jeff Boyce]

Doug

Why? As in "why do you want to break the 'delivery address' field down into
those three components?" What business need have your customers expressed
re: mailing labels that will require this design?

And what will you do with this design when there are more than three
"fields" into which an address could be parsed? More than 4? More than 5?!

And how about an address that doesn't use numerics at all -- for example:
One Century Plaza

I'm not saying it shouldn't be done, but I am saying that it shouldn't be
done without a good reason.

The only reason you provided so far was "mailing labels" -- how will that
require such detail?

Just curious!

Jeff Boyce
<Access MVP>