Search my customer data by plotting area on map

[11Edward23]

Hi all, as you will be able to tell by the nature of this question I am new to MS Access. I am in the process of designing a database for my cleaning business and want to be able to perform the following query. I will first start with an explanation of what I want to achieve practically based upon how the business operates.



We obviously have a number of cleaners on our books, all of whom are based in various locations within a certain geographical area. Each cleaner varies in how far they can (or are willing to) travel, and therefore the number of customers that they can reach varies. New customers often have a fixed idea of a day and time slot that they wish a clean to take place, so matching a cleaner's work/location schedule and/or home location to the customer's location is key. This way we can quickly contact our cleaners and allocate them to the new customer in the most efficient way possible.

I would therefore like, for any cleaner on our books, to input a radius from any given location (i.e. the cleaner's home or any other location that they happen to be working at) to which they are willing to travel by plotting this on a Google map. The desired effect would be that when a new customer calls we are instantly able to plot their location and review a list of cleaners filtered by availability to work and availability by proximity to location. It would be also useful to have a secondary view of cleaners falling outside the parameters they have set.

How do I achieve this?

[ItsMe]

Sounds like you will need Geofencing interface with Google Maps or Microsoft MapPoint

You can register for an API key with Google. They will allow a certain number of queries to their DB from your API key that will be assigned to you at the time of registration. I think they allow 20,000 queries per day, per Key. Google examples are going to be heavy in Java and HTML. You will need to be able to translate examples offered to manage your interface using MSXML objects via VBA. There are XML examples available from Google directly.

MapPoint has to be purchased for each computer that will run your App to get the full benefits of MapPoint. The API will be more intuitive to Access. There will be a library reference available once MapPoint is installed.
http://www.mapforums.com/forum.php

[11Edward23]

This is great, thanks very much for your reply. As a newbie to Access, do you know of any resources/what I should study to enable me to achieve the geofence interface with Google Maps?

[ItsMe]

Not specifically to the Geofence. I would start with understanding Geocoding basics. Creating a table and collecting the Lat and Lon coordinates for a few addresses would be a good exercise. I have played around with it a little. You get a return from google in the form of XML. You will have to parse the XML to get the info you need.
https://developers.google.com/maps/d...ocoding/?hl=en

You will need to understand VBA and be able to create string arrays, iterate the arrays, parse the strings,
You will need to be familiar with the various Internet Explorer libraries available in Access
You will need to understand creating and working with MSXML objects

[June7]

Proximity analysis is not simple. ESRI ArcMap application has a tool to do that but that is a several thousand dollars product. Allows analysis like: "Find the cleaners within 5 miles of this customer address". Data can be exported to XML file and the XML file can be uploaded to Google Earth or other on-line mapping.

Look at this http://webhelp.esri.com/arcgisdeskto...imity_Analysis

MS MapPoint might have the same capability and costs $299.

API programming of this sort of analysis I expect would be very, very difficult.

[11Edward23]

Thanks, looks like Mappoint will be the best option.

[ItsMe]

I have purchased from Mr. Eric Frost previously. Post #2 has a link to his website and you can purchase MapPoint from there. I have no affiliation and got good service from him before.

I was playing around with mappoint and was able to place circles on a map and calculate distances as the crow flies from address' and also lat, lon coordinates. You can save, close, edit, etc. map files from Access. I did not see a need for Geofence for your app.

You can also create DAO recordsets of the data on a saved map file. You can iterate through locations, pushpins, etc. using DAO.

[ItsMe]

Here is some sample code to interact with MapPoint

Dim objApp As MapPoint.Application
Dim objMap As MapPoint.MAP
Dim objFind1 As MapPoint.FindResults
Dim objFind2 As MapPoint.FindResults
Dim objLoc1 As MapPoint.Location
Dim objLoc2 As MapPoint.Location
Set objApp = CreateObject("MapPoint.Application")
objApp.Visible = True
Set objMap = objApp.ActiveMap
Set objFind1 = objMap.FindAddressResults("1234 Any Street", "Some City", , "NY", "12345")
Set objLoc1 = objFind1(1)
Set objFind2 = objMap.FindAddressResults("1234 That Street", "Other City", , "NY", "12345")
Set objLoc2 = objFind2(1)
Debug.Print "objLoc1.StreetAddress.Value = " & objLoc1.StreetAddress
Debug.Print "objLoc1.Latitude = " & objLoc1.Latitude
Debug.Print "objLoc1.Longitude = " & objLoc1.Longitude
Debug.Print "objLoc1.Type = " & objLoc1.Type
Debug.Print "objFind1.ResultsQuality = " & objFind1.ResultsQuality
Debug.Print "_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_*_"
Debug.Print "objLoc2.StreetAddress.Value = " & objLoc2.StreetAddress
Debug.Print "objLoc2.Latitude = " & objLoc2.Latitude
Debug.Print "objLoc2.Longitude = " & objLoc2.Longitude
Debug.Print "objLoc2.Type = " & objLoc2.Type
Debug.Print "objFind2.ResultsQuality = " & objFind2.ResultsQuality
Debug.Print "Distance = " & objMap.Distance(objLoc1, objLoc2)   'As the Crow Flies
Set objApp = Nothing
Set objMap = Nothing
Set objFind1 = Nothing
Set objLoc1 = Nothing
Set objFind2 = Nothing
Set objLoc2 = Nothing

[11Edward23]

Awesome, thats really helpful thanks very much. Looks like a steep learning curve!

[ItsMe]

There is more stuff in there than need be but, it illustrates some of the objects and methods available. But yah, you will have a few lines of code in your modules.

[ItsMe]

Here is a snippit that will place a circle on your map.

Dim objApp As MapPoint.Application
    Set objApp = CreateObject("MapPoint.Application")
        objApp.Visible = True
Dim objMap As MapPoint.MAP
    Set objMap = objApp.ActiveMap
Dim objFind As MapPoint.FindResults
    Set objFind = objMap.FindAddressResults("1234 Any Street", "Some City", , "NY", "12345")
Dim objLoc As MapPoint.Location
    Set objLoc = objFind(1)
    objLoc.Select
    
Dim objShape As MapPoint.Shape
    Set objShape = objMap.Shapes.AddShape(geoShapeOval, objLoc, 2, 2)
        objShape.SizeVisible = True

[11Edward23]

Again, really appreciate that. I'm downloading it now and will let you know how I get on. Cheers