A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sorting Addresses in Microsoft Access



 
 
Thread Tools Display Modes
  #1  
Old August 28th, 2004, 01:36 PM
Will Corby via AdminLife
external usenet poster
 
Posts: n/a
Default 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  
Old August 28th, 2004, 05:12 PM
fredg
external usenet poster
 
Posts: n/a
Default

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  
Old August 28th, 2004, 05:12 PM
Steve Schapel
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 10:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.