proffittware
asked on
separate street number from street name for sorting purposes in Access
I have street addresses in one field, which includes the number and the name.
I want to separate the number of the street address from the rest and have two fields so that sorting will be more accurate.
The numbers are separated by a space in the address.
I would like to do an update query to fix this problem.
Can anyone help ? Or have any other ideas ?
I want to separate the number of the street address from the rest and have two fields so that sorting will be more accurate.
The numbers are separated by a space in the address.
I would like to do an update query to fix this problem.
Can anyone help ? Or have any other ideas ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hello proffittware,
I think this question may be subtly more difficult than you think it is, and the main reason why has everything
to do with JVWC's comment.
"Street numbers" simply cannot be counted on to be, um, numbers; in addition to JVWC's examples you can
also have items like "1234A SomeNameHere Street".
Thus, if you are going to extract that data element into its own column, then you will have to use a text data
type for it.
Now sorting, if you choose to sort, becomes an adventure. Consider these two addresses:
33 Main Street
101A Main Street
If you try sorting on the "number" portion, which should come first? In a text sort, "101A" will sort before "33".
Then when you try to sort the streets, consider these pairs:
33 Main Street N
33 N Main St
33 2nd Ave
33 Second Ave
Regards,
Patrick
I think this question may be subtly more difficult than you think it is, and the main reason why has everything
to do with JVWC's comment.
"Street numbers" simply cannot be counted on to be, um, numbers; in addition to JVWC's examples you can
also have items like "1234A SomeNameHere Street".
Thus, if you are going to extract that data element into its own column, then you will have to use a text data
type for it.
Now sorting, if you choose to sort, becomes an adventure. Consider these two addresses:
33 Main Street
101A Main Street
If you try sorting on the "number" portion, which should come first? In a text sort, "101A" will sort before "33".
Then when you try to sort the streets, consider these pairs:
33 Main Street N
33 N Main St
33 2nd Ave
33 Second Ave
Regards,
Patrick
Might I also point out::
Unit 5, 23 Main Street
or
Suite 5A
5th Floor
289 George Street
Unit 5, 23 Main Street
or
Suite 5A
5th Floor
289 George Street
Orcbighter,
Just gets better and better, doesn't it?
:)
Patrick
Just gets better and better, doesn't it?
:)
Patrick
A can of worms, opened, turns out to be a barrel of snakes.
Given that the original bad design has allowed a complex address to be stored in a single field, applying an algorithm to restore some semblance of order is not something that will be achieved via a simple update query.
You have a single string field (I presume) to hold the address that really is at least 5 or 6 separate fields
eg: working backwards:-
postcode, country, state, City, suburb, street address2, Street Address1, [street number]
* square brackets indicate an optional choice of storing the street number separately
If you can modify the database and these changes need to persist beyond this single exercise, I would suggest a data conversion: Not a simple exercise.
Firstly, I would suggest leaving the old address field alone (who knows what other software might access it) and add a number of new fields along the lines outlined above.
Secondly, design some queries to extract out the values for these fields from the old address. It will probably require a number of functions to do this.
Thirdly, generate a report to verify that your extracted values are correct and not garbage.
Fourth, design your search to work on these new fields.
Even if you can only extract out the suburb, city, state and postcode information and leave the rest of the street address as a single lump, you will have improved your search accuracy by an order of magnitude.
Given that the original bad design has allowed a complex address to be stored in a single field, applying an algorithm to restore some semblance of order is not something that will be achieved via a simple update query.
You have a single string field (I presume) to hold the address that really is at least 5 or 6 separate fields
eg: working backwards:-
postcode, country, state, City, suburb, street address2, Street Address1, [street number]
* square brackets indicate an optional choice of storing the street number separately
If you can modify the database and these changes need to persist beyond this single exercise, I would suggest a data conversion: Not a simple exercise.
Firstly, I would suggest leaving the old address field alone (who knows what other software might access it) and add a number of new fields along the lines outlined above.
Secondly, design some queries to extract out the values for these fields from the old address. It will probably require a number of functions to do this.
Thirdly, generate a report to verify that your extracted values are correct and not garbage.
Fourth, design your search to work on these new fields.
Even if you can only extract out the suburb, city, state and postcode information and leave the rest of the street address as a single lump, you will have improved your search accuracy by an order of magnitude.
I can't help myself...
What about PO Box 88, ...
:-)
JC
What about PO Box 88, ...
:-)
JC
don't forget
GPO Box 88,
or even
G.P.O. Box 88
GPO Box 88,
or even
G.P.O. Box 88
ASKER
Thanks to everyone for responding. But my application was a controlled address environment.
JC