If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting Addresses in Microsoft Access
(Type your message here)
Hi I have a database which is replacing a card system with the= customers name, address and ref no=2E on it=2E I need to sort it by= address, but access is driving me crazy=2E It needs to be sorted= by street name and then number, but I can't seem to get it to= work=2E I've tried searching the forums but to no avail=2E I'm= guessing maybe there is some quick fix, but I can't seem to find= it=2E Thanks in anticipation Will PS i'm using Access 2000 -------------------------------- From: Will Corby ----------------------- Posted by a user from AdminLife (http://www=2Eadminlife=2Ecom/) Id/R5TfhwoXUuyczP3CIdEIg=3D=3D/Id |
#2
|
|||
|
|||
On Sat, 28 Aug 2004 05:36:09 -0700, Will Corby via AdminLife wrote:
(Type your message here) Hi I have a database which is replacing a card system with the customers name, address and ref no. on it. I need to sort it by address, but access is driving me crazy. It needs to be sorted by street name and then number, but I can't seem to get it to work. I've tried searching the forums but to no avail. I'm guessing maybe there is some quick fix, but I can't seem to find it. Thanks in anticipation Will PS i'm using Access 2000 -------------------------------- From: Will Corby ----------------------- Posted by a user from AdminLife (http://www.adminlife.com/) Id/R5TfhwoXUuyczP3CIdEIg==/Id Addresses come in all sorts of different configurations. 123 Main St. 123 So. Main St. 123 E. Main St. 123 E. Main Street 123 Main St. NW 123 No. Main St. 123 Main St. apt. 32 123 C E. Main Avenue 123 1/2 So. Main St. 10158 Main St. 658 So. Elm St. 853 No. Elm St. etc. How would you wish the above Street names sorted? According to the full street name, i.e. No. Main St.? The sort order will be E. Main Avenue, Main St., Main St. apt. 32, Main Street, No. Elm St., No. Main St., So. Elm St., So. Main St., etc. According to street direction, So. E. W. No., then then the Street name? If so, then the sort will be Main St., Main St. apt. 32, E. Main St., E. Main Street, No. Elm Ave., No. Elm St., No. Main St., So. Elm St., So. Main St., etc. I may have missed a street, but I think you can get the idea. Get the address street field separated from it's house number. Then decide if you want to further separate the street direction from the street, then you can simply sort on the street address, then on the house number; or on the street direction, street name, house number. But the data should be in separate fields. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#3
|
|||
|
|||
Will,
The most reliable approach would be to put the street name and number in separate fields. Then you just have to define which fields to sort by, and problem solved. If, for some reason, the street name and number must be in the same field, it will only be possible to resolve it if there is consistency in the structure of the data. For example, if all the addresses are always like number space street then you could make a calculated field in your query to sort on, something like this... FirstSort: Mid([Address],InStr([Address]," ")+1) .... and then do your sorting on this field first, and your existing Address field second. -- Steve Schapel, Microsoft Access MVP Will Corby via AdminLife wrote: (Type your message here) Hi I have a database which is replacing a card system with the customers name, address and ref no. on it. I need to sort it by address, but access is driving me crazy. It needs to be sorted by street name and then number, but I can't seem to get it to work. I've tried searching the forums but to no avail. I'm guessing maybe there is some quick fix, but I can't seem to find it. Thanks in anticipation Will PS i'm using Access 2000 -------------------------------- From: Will Corby |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
Access user profile | Michael Breitsameter | General Discussion | 7 | July 23rd, 2004 07:47 AM |
Useless Access 2003 | tired, angry, sucidial and bored | General Discussion | 10 | July 21st, 2004 11:52 PM |
How Microsoft Office Tech Support (In Reality Convergys of Ohio) is a Miserable Failure and Oxymoron | Chad Harris | General Discussions | 0 | May 29th, 2004 09:54 AM |
office 2000 remove tools | bud | Setup, Installing & Configuration | 1 | May 27th, 2004 10:40 AM |