Application for "Find a Dealer Near You"

[Joe]

I need an Access application that can search dealers from my dealer
list, in a 30 miles distance from the user.

You may see many applications like this on the web if you search under
"find a dealer near you". Here is one I found:
http://www.shakespeare-antennas.com/...alersearch.asp

But I do not want to have it on the web, I want to create it on
access.
I think will need at least two tables:
One is a table of each of our dealers, including their zip codes.
The other one is the entire zip database whick include all zip codes
in the USA and Canada, including the lat and long for each.

Then, when the user enters the zip code of the person who wants to
find a dealer, the application will find its lat and long from the
second table. It will then "open" an area of 30 miles radius from this
lat and long, and will find all the dealers' ZIPs in that area.
Actulally,
the lat and long of each.

Then it will calculate the air distance from the user ZIP codes to
each of the Dealers ZIP codes.
Answers will be appreciated.

Aaron
ga*****@aol.com

[Alan Webb]

Joe,
Yeah, well . . . rolling your own is almost always more expensive, more time
consuming and consequently not a savvy career move. Microsoft publishes a
CD-ROM based version of its MapPoint web service that you can purchase at
your local computer retailer. It does exactly this and for a lot less than
the labor rate of most of us for something like this. But you would not
have posted a request like this if you had the good sense to buy something
that works. So, the technique goes like this. A geographic database is an
object database where each polygon of the map is an instance of an object.
All the objects inherit common characteristics like their center point, the
longitude & latitude at the center point, distance to an edge, etc. One of
the differences is that object databases are not 2 dimensional matrices of
rows and columns but 3 dimensional cubes of object instances. Things get
way more interesting and powerful from there. So, if this was to be an
Access database it would be a table of object instances with at least two
other tables--an intersection entity tieing properties to instances and a
table of properties. That's just the begining. There is a lot of code to
work out from there.
If . . . you are enough of a masochist to take this on then do the right
thing and plan it out in a flowchart software like Visio. But . . . if you
came to me with a check for my labor and asked me to write this I'd probably
take the check, go to my local software retailer, buy an off-the-shelf
product for you and give you the change. This can be done but it isn't a
trivial project.

"Joe" <ga*****@aol.co m> wrote in message
news:b4******** *************** ***@posting.goo gle.com...

I need an Access application that can search dealers from my dealer
list, in a 30 miles distance from the user.

You may see many applications like this on the web if you search under
"find a dealer near you". Here is one I found:
http://www.shakespeare-antennas.com/...alersearch.asp

But I do not want to have it on the web, I want to create it on
access.
I think will need at least two tables:
One is a table of each of our dealers, including their zip codes.
The other one is the entire zip database whick include all zip codes
in the USA and Canada, including the lat and long for each.

Then, when the user enters the zip code of the person who wants to
find a dealer, the application will find its lat and long from the
second table. It will then "open" an area of 30 miles radius from this
lat and long, and will find all the dealers' ZIPs in that area.
Actulally,
the lat and long of each.

Then it will calculate the air distance from the user ZIP codes to
each of the Dealers ZIP codes.
Answers will be appreciated.

Aaron
ga*****@aol.com

[Galsaba]

Actually, I bought MapPoint.
But I thought it is to kill a fly with a cannon.
I do not need the map, really. What I need is just an access application, you
will enter your zip , click ok, and the query will be executed to show you list
of dealers near you.

I am not sure why it should be 3 dim, as I am looking for air distance only.

Seems to me that it should be simple application, the way I described it on my
first posting.

Regards,

Aaron

[Alan Webb]

Galsaba,
Right, but geo-databases are not relational, they are object based.
Different creatures, really. If you have MapPoint, then you can use the VB
hooks in it to instantiate MapPoint and have it find what you want. My main
point was that while it may seem like a simple thing to code it really isn't
and you are much better off trying to work with an off-the-shelf product.

"Galsaba" <ga*****@aol.co m> wrote in message
news:20******** *************** ****@mb-m15.aol.com...

Actually, I bought MapPoint.
But I thought it is to kill a fly with a cannon.
I do not need the map, really. What I need is just an access application, you will enter your zip , click ok, and the query will be executed to show you list of dealers near you.

I am not sure why it should be 3 dim, as I am looking for air distance only.
Seems to me that it should be simple application, the way I described it on my first posting.

Regards,

Aaron

[Tony Toews]

ga*****@aol.com (Joe) wrote:

Then it will calculate the air distance from the user ZIP codes to
each of the Dealers ZIP codes.

How big are zip code zones? I suspect some are significantly larger than 30 miles.
And even if smaller can introduce some error into your algorithm.

I'd sure go with MapPoint.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Galsaba]

I tried MapPoint. Seems to me too complex and slow. It gives me more than I
need, but it is too general, and that's why slow. I do not need the map itself.
The only thing I need is a simple application that will calculate it.

Aaron

[rkc]

"Galsaba" <ga*****@aol.co m> wrote in message
news:20******** *************** ****@mb-m18.aol.com...

I tried MapPoint. Seems to me too complex and slow. It gives me more than I need, but it is too general, and that's why slow. I do not need the map itself. The only thing I need is a simple application that will calculate it.

Whatever you do you're going to have to spend around $60 US to get
a database with zipcode and latitude/longitude data.

Have you tried Google? There are plenty of hits on Visual Basic
code to calculate distances between two points. If my memory
serves correctly Lyle Fairfield has posted code to this newsgroup.
Other also most likely. Although what you probably want to do
is calculate maximum latitude and longitude points that are 30 miles
from a given point. I believe Alan Webb was trying to point out to
you that that may not be as trivial as it sounds.

[Alan Webb]

"Galsaba" <ga*****@aol.co m> wrote in message
news:20******** *************** ****@mb-m18.aol.com...

I tried MapPoint. Seems to me too complex and slow. It gives me more than I need, but it is too general, and that's why slow. I do not need the map itself. The only thing I need is a simple application that will calculate it.

Based on what dataset? Are you going to develop your own schema & populate
it with your own zip code and distance information? Why ignore what has
already been done, is relatively cheap compared to paying a programmer
$150.00/hr for 80 hours (two weeks, and that's being naively optimistic), or
about $12,000.00 to build this? I'll tell you what, I bet a steak dinner at
Richbrau in Richmond, Va. (you have to get here to collect) that what you
want can't be done for less than what you paid for MapPoint. Even owning
MapPoint still means you still have some code to write to tie your app to an
instance of MapPoint.

[Carol and Doug Hutcheson]

Joe wrote:

I need an Access application that can search dealers from my dealer
list, in a 30 miles distance from the user.

You may see many applications like this on the web if you search under
"find a dealer near you". Here is one I found:
http://www.shakespeare-antennas.com/...alersearch.asp

But I do not want to have it on the web, I want to create it on
access.
I think will need at least two tables:
One is a table of each of our dealers, including their zip codes.
The other one is the entire zip database whick include all zip codes
in the USA and Canada, including the lat and long for each.

Then, when the user enters the zip code of the person who wants to
find a dealer, the application will find its lat and long from the
second table. It will then "open" an area of 30 miles radius from this
lat and long, and will find all the dealers' ZIPs in that area.
Actulally,
the lat and long of each.

Then it will calculate the air distance from the user ZIP codes to
each of the Dealers ZIP codes.
Answers will be appreciated.

Aaron
ga*****@aol.com

Aaron,
Assuming you get a copy of the US Postal Codes database suggested
elsewhere in this thread (and assuming you are looking for a US locaised
solution), the following VB6 function was posted by f_rodriguez
(http://www.experts-exchange.com/M_2148837.html) at
http://www.experts-exchange.com/Data...20383758.html:

*************** *************** *************** *************** *******
Public Const gEARTH_RADIUS_M ETRES = 6378007
Public Const gEARTH_CIRCUM_M ETRES = gEARTH_RADIUS_M ETRES * 2 * gPI
Public Const gMETRES_PER_LAT _DEGREES = 111113.519

Public Function GetDistance(dX1 As Double, dY1 As Double, dX2 As Double,
dY2 As Double) As Double
Dim dDeltaX As Double
Dim dDeltaY As Double
Dim dDeltaXMeters As Double
Dim dDeltaYMeters As Double
Dim dMetersPerDegre eLong As Double
Dim dCenterY As Double

dDeltaX = Abs(dX2 - dX1)
dDeltaY = Abs(dY2 - dY1)
dCenterY = (dY1 + dY2) / 2
dMetersPerDegre eLong = MetresPerDegree Long(dCenterY)
dDeltaXMeters = dDeltaX * dMetersPerDegre eLong
dDeltaYMeters = dDeltaY * gMETRES_PER_LAT _DEGREES
GetDistance = Sqr(dDeltaXMete rs ^ 2 + dDeltaYMeters ^ 2)
End Function

Public Function MetresPerDegree Long(ByVal dLat As Double)
MetresPerDegree Long = (Cos(dLat * (gPI / 180)) *
gEARTH_CIRCUM_M ETRES) / 360
End Function
*************** *************** *************** *************** *******

HTH
Doug