Postcode Analysis - Nearest Venue

[NumberXYZ]

Hello

I currently have two tables - one with a list of ten customers and their postcodes (eastings & northings)- the other with a list of 100 stores and their postcodes (eastings & northings). I was recently shown a 'fdist' query where you could put a distance parameter into the query. This would, for example, append a third table with a list of the customers with all the stores that fall within the specified distance radius. Is it possible to query so that it only brings back the nearest store to that customer.

The fdist code was put into the 'field description' and the distance into 'criteria' while using an append query. Any simple answers of the nearest venue for a 'non-programmer'.

Cheers in advance.

 

[MichaelRed]

Generally, the postcode will only get an approximation of closeness. To get the actual 'shortest' straight line disctance you need the lat/lon of the two points and some simple trig. The trig has been posted in Tex-Tips (MS. A. or VB), but I do not recall the specific forum / thread, but believe the tere "Zip Code" (U.S. version of postcode)is used.

To get the nearest 'driving distance', you need to interface with one of the street map/driving directions programs, but do not need the lat/lon information. I have used Ms. MapPoint, but the gossip says the street information for non-US areas is not up to the same level of detail / accuracy as the US map, which still exhibits some errors.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[NumberXYZ]

Thanks for the reply. I've managed to calculate the distance between two postcodes (northing & easting) useing 'fdist'. This gives me a table where each member has a km distance for each of the 100 venues. I'm now simply trying to do a query that only displays one record for each customer. I've tried 'Group By' for Customer ID & Venue and 'Min' for distance but this obviously still displays all the different Venues. Any ideas?

 

[Golom]

Select Top 1 ... fields ...
From ...
ORDER BY distance