Database Storage - Inherited data

[Doug]

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.

 

[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>