X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Split Full Location Field Into Component Parts In New Table, Access 2016    
post Aug 14 2019, 10:27 AM

Posts: 79
Joined: 16-March 15
From: Chautauqua, NY

Hello, I inherited a db with a table that has a location (address) table with one field. The field currently has one of the following examples of records .i.e:

123 Main St.
77 Hedding
20 Elm Ln. #B3
Rt. 394 & Park Place
77 Mission Ave./Hotel

I need to split this field into 4 fields:

address number i.e. 123
street name i.e. Main St. or Hedding
unit # i.e. #B3
alias (i.e. Hotel) i.e. /Hotel (without the /)

The table has over 700 records and is growing by 300 a year and I do not want to do this by hand. I figured out how to get the number out in a column by itself with a query:

locationSplit: IIf(InStr([callLocation]," ")>0,Left([callLocation],InStr([callLocation]," ")-1),[callLocation])

The only problem with this approach is that I still have a hundred entries that are the fourth line above (Rt. 394 & Park Place) and now they are split out as well.

So I want to be able to say if the record starts with a number, then do the above code, else do not. Then I need to create something like the above to get the right hand text back to but not including the / in a new field if there is a /. And hopefully delete the /. The unit # field is only in a few records so I could do them by hand if necessary. Then I need to split the street name out.

I want to end up with a table that has the street number if any, then the street name(s), then the unit number if any, then the alias if any.

I am thinking this is going to take several steps.

Please let me know if I can make any of this clearer.

Thanks for the help, Scott
Go to the top of the page
post Aug 14 2019, 10:46 AM

Access Wiki and Forums Moderator
Posts: 76,315
Joined: 19-June 07
From: SunnySandyEggo

Hi. I also think it might take several steps to get it correct. You might consider using/creating a function to do this to avoid having to construct very long and complicated expressions in your query.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Aug 14 2019, 11:11 AM

Posts: 103
Joined: 29-September 10
From: AZ

This is going to require a number of parsing routines.
Looking at the data provided going to be a tough go.
E.g., What if someone entered "One Main Place" instead of "1 Main Place".
Nothing unique in the dataset that I can see.
There is NOT always a solution.

Just out of curious why 4 fields?
This post has been edited by dw85745: Aug 14 2019, 11:11 AM
Go to the top of the page
post Aug 14 2019, 01:39 PM

UtterAccess VIP
Posts: 7,962
Joined: 24-May 10
From: Downeast Maine

Sometimes you need to do as many as you can according to a rule, and look at what is left. For instance, to get the number you could have:
IIf(IsNumeric(AddressField),Left(AddressField,InStr(AddressField," ") -1),Null)

That could be the expression to update a new StreetNumber field in the table. Another expression could be in a new Remainder field:

IIf(IsNumeric(AddressField),Mid(AddressField,InStr(AddressField," ") + 1),AddressField)

For 123 Main St. that should give you 123 in the StreetNumber field, and Main St. in the Remainder field. The next time through (to extract the street name, for instance), use the Remainder field).

For records that do not have anything in the StreetNumber field, the first value in AddressField was not numeric. Unless there are a lot of such records that follow a pattern, update those records manually, and put what's left into the Remainder field.

Continue until you have broken down the address to the extent practical using code.
Go to the top of the page
post Aug 14 2019, 01:55 PM

Posts: 103
Joined: 29-September 10
From: AZ

Been thinking on your problem Don't believe there is a FULL COMPUTER CODE solution to it, but you may be able to
narrow things down to isolate records if you objective is to clean up the table and set some standard.
What I would do is:
1) Set up a table with 6 or 7 columns.
Column1 = Flag
Column2 = Original Field Info
Column3 = Original Corrected
Column 4 = Balance Left
Column 5 = Begin #s
Colunm 6 = Address
Column 7 = Suite/Appt
Column 8 = Other

I'd FIrst Write All Records from Column2 to Column3.
I'd then make a pass of Column2 and when I reached the first blank space
write that number value into Column5 and what's left (balance of text) into Column4
I'd then make a second pass and see if there
is any text in Column 5. (E.G. Rt or Rt. or Route would be text). If Test Identified
Id set the Flag in Column1. You could then dump all Flagged Records and manually
examine them, make correction as needed in Column3 and Column5.

Clear the Flag

I'd then look at Column4 and Check for Suite, #, No, No. or anything else you can think of.
I'f then write everything between the first character in the field and those points to Column6,
delete that information from Column4, and Set the Flag where Suite, #, etc was identified.
theN manually examine the recordset.

Clear the Flag
Do the last.

Hopefully gives you an idea on how to approach. Have to use your ingenuity.
This post has been edited by dw85745: Aug 14 2019, 01:59 PM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    19th September 2019 - 02:01 PM