[AccessD] Numbers within a street

Why Excel?  It's much easier in Access.  I do this a lot.

1. Add the new required new fields.
2. Run a query to update the fields where everything before the first space is numeric using 
suitable functions.

3. Filter the table to only show records where the new fields are empty. 
4. Look for the next  common pattern and update all records where the new fields are empty 
with an appropriate function.

Repeat 3 and 4 until you get down to a small subset that is easier to update manually.

-- 
Stuart

On 15 Sep 2011 at 9:42, Darryl Collins wrote:

> Arthur
> 
> I would sort the data first.
> 
> Then I would export it to Excel - your issue here will be the size of
> the dataset although if you are using XL2007+ this is less of a hurdle
> 
> By having the data sorted you should be able to get the low hanging
> fruit early. That is the "24 MyStreet Ave" and deal with them all
> pretty much in one go.
> 
> You can then use the "Text to Columns" function to split the data.
> 
> If you do it in groups you might find you get a lot of it done easily.
> 
> Given how messy this sort of data is going to be, I feel eyeballing it
> manually is going to be your best approach.  This will allow you to
> group it into data chucks that you can then run a function or script
> over and get a result.
> 
> Anyway. Good luck. It is usually a messy process as there are many
> dissimilar valid combinations with address
> 
> Cheers
> Darryl
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur
> Fuller Sent: Thursday, 15 September 2011 6:31 AM To: Access Developers
> discussion and problem solving Subject: [AccessD] Numbers within a
> street
> 
> I am working on a political campaign management app. The thing I need
> to do is post reports that identify the even and odd numbers on a
> given street. I inherited the db and have freedom to change it (it was
> written by amateurs and they know it and there are no hard feelings if
> I make a change, insofar as said change increases productivity).
> 
> So... given a current field called StreetAddress, I want to break it
> into two fields, StreetNumber and StreetAddress, so that I can filter
> the even numbers for one report  and the odd numbers for an identical
> report. How can I intelligently extract the data and populate my new
> columns?
> 
> Example data:
> 
> 123 Normal Street ' easy
> 234A Abnormal Street ' a tad trickier
> 2333-1070 Queen Street  ' the 2333 part should fall into the
> StreetNumber field and the rest into the StreetAddress field.
> 
> Any clever ideas how I might achieve this?
> 
> TIA,
> Arthur
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>