Help with a portal/relationship setup

Discussion created by ms on Apr 23, 2012
Latest reply on Apr 23, 2012 by ms

Hi all-

I'm working on a database wherein I need to determine what locations are within a given range of the selected location. I'm using zip codes for the calculations, and I have the distances calculated by the latitude and longitude of the center of the zip codes. I have the raw coordinates as well as pre-calculated mileages from zip to zip, e.g., zipA to zipB is 5 miles, zipA to ZipC is 10 miles, zipB to zipC is 5 Miles, and so on. I have this data for several thousand locations across several thousand zip codes. There is more than one location in many of the zip codes.


I'm trying to set up a portal such that when I am looking at a location, the portal displays every location within X miles, where X is set by another field, maxDistance.


The problem that I'm running into is how to key the relationship effectively. I started with list of (millions) of combinations of zip codes - zipA, mileage, zipB - but because zipA appears multiple times in each table, I can't key on it. I then generated a table with records for each zip, and fields for each other zip with the distance contained within. Here, though, I can't figure out how to key on a variable field to look up the distances.


I'm hoping that one of you has an idea for an approach to this problem - I've gotten my head stuck in the things I've already tried, and I am having trouble thinking of new approaches.