Interesting reading. I knew it would be a challenge, but that was depressing. I think for the 2000 addresses I have to do, this may be easiest if I create a form for those dep's to do it themselves. In my case, it only has to be done to get the data converted and all new or maintained addresses will be in compliance. Problem is, I need to move the data a few times before go live. Now, I think I will separate the streets into a street file to prevent adding street names as random. Now I need to decide if I should store the street name in the location file or a foreign key. As often as we all deal with addresses, it is too bad there isn't more of a standard implementation. Sometimes the addresses are a factor and need to aggregated and other times no one cares I guess.

Was easier when I used the GPS as was mentioned in the other thread. When the truck drove by the stop if the GPS location wasn't present in the file, it added it. But in this case, I don't have my application in trucks driving by the locations.
Tim

>It's not that simple as it seems. Check <B>Parsing a Street Address into Separate Fields</B> Thread #1144790
>
>>
>>I have a database I am converting to a new application database and I need to move the addresses. Currently the file has addresses in the client file as:
>>

>>Address
>>Address2
>>City
>>State
>>Zip5
>>

>>An example of data in those files looks like this:
>>

>>15329 SCENIC VIEW DR       NULL             BULLARD     TX     75757
>>22617 POST OAK DR #A       NULL              FLINT             TX     75762
>>16628 (TR #1) CR 1100         NULL              FLINT             TX     75762
>>RT 2 BOX 171                          NULL               BULLARD     TX     75757
>>159 C R 3327                         PO BOX 163     CUNEY          TX    75759
>>

>>For the most part, I will probably ignore what is in the Address 2 column as it appears to be a mailing address or a repeat of an apartment number. I can tag the Record Status column in this table as needing verification for address or something for those minor cases.
>>
>>What I need to do is move the Addresses out to a new table called Location.
>>

>>LocationID                 UniqueIdentifier
>>LocationTypeID         Int
>>AddressNumber       varchar(15)
>>StreetName                varchar(50)
>>ApartmentNumber     varchar(10)
>>PostalCode                 int
>>

>>
>>Postal Codes are already normalized, so I will have to look those up and replace the proper integer foreign key id.
>>As I move an address out, I have added a column "LocationID" in the original table so that I can connect the address back. The problem is as you can see the addresses are all formatted free form except there seems to be some consistency with upper case. Plus there are certainly many that are duplicate addresses as those have moved and not updated their addresses.
>>
>>There are about 2000 rows in this table, so it isn't entirely out of the question to create a form for some data entry person to go through them, but I would like to get it most of the way separated if I can.
>>Any ideas are appreciated.
>>Thanks
>>Tim