Trying to do a mail/delivery route

[Fdisk]

I hate a database with a bunch of people and their address. Their address is divided as follows:

House number, Direction (North West, South West, etc), Street Number, Street Type, zip code

What I need to do is to create a query/report where I get a list of people that I need to go see throughout the city but to shorten the process I would like to have the list ordered so it's efficient and I don't find myself going back 10 blocks to go see someone who lives next door to a person who was 2 spots higher on the list.

Here's the problem I have. The city I live is divided as a coordinates plane, streets run east-west and avenues run north-south. The problem is that avenues are classified as Courth, Road, Avenue and Place. Anything not in that category goes east-west (horizontally if you are looking at a map)

Right now I'm ordering as follows:

zip, street number, house number, street type

The problem is that some streets are getting mixed with the avenues.

Can you guys help me figure out how to effectively come up with an efficient route to visit these people?

Thanks in advance for any help and let me know if further information is needed. I have the list in Excel too but I'm much more familiar and comfortable with Access.

 

[rockman]

I could be wrong but I think this problem is much too complex for a query to handle. Even if you group by zip code alone, more information is needed to assure the logic path you desire. Imagine this mythical city's zip code arrangement:

   85000    85001   85002    85003
   85004    85005   85006    85007

The quickest route is not sequential zip codes but rather:

85000
85001
85002
85003
85007
85006
85005
85004

A shortest route algorithm is a very complex operation. Perhaps an advanced mathematics book is a good place to start. Even then, while two locations may have a shorter mileage between them, they may be practically "farther apart" than two locations separated by an interstate. Furthermore, in my example I traveled in a clockwise fashion, but let's say it's 5 p.m. and all the traffic is traveling in a clockwise fashion in town. It would then be faster to travel the route in a counter-clockwise fashion (on empty streets).

Bottom line: The human brain is much better at solving connecting dots than any routine computer program. I think the reasonable approach is to group the locations by zip code and let the driver do the thinking. If you can devise a map and mark the locations this would aid the driver --- but even this is not an easy task.

IMHO,
Jeff

PS Was this a freudian slip?...

I hate a database with a bunch of people and their address.

 

[Fdisk]

Thanks for the help

hahahahaha you just made me laugh out loud. I'll be damned, I guess it was a freudean slip after all. I love databases, Access and SQL, I love it, and this database is one of my babies so I don't hate it, but I do hate this problem I've encountered. I'm the type of person who loses sleep over little bumps in the road like this.

I know there's a way of doing this since the post office has to do it somehow, as well as Fedex, UPS and other couriers. However it might, as you stated in your post be much too complex [for my feeble mind to tackle ]. The closest I have come is the order I wrote before, but it's still not good enough.

I believe the post office has a service that will do this for like $200, but I work for a non-profitable entity, so that's a whole lot of money to spend. I'll try it the old fashion way of routing using a map.

Thank you very much for your reply, I knew when I stumbled upon this forum that someone would be able to look at it and honestly try to help. I'm actually surprised at how fast it was. I hope to stick around, maybe help a newbie or two with basic questions