Excel Table as Query Source

[dd92251]

I have an Access database that contains Radio Station Call Letters and their associated radio tower coordinates. I will be receiving an Excel Table list of cellphone towers and their coordinates.

What I need to accomplish is this: Each cellphone tower coordinate has to be compared to the Radio Station coordinates in the database and the radio station towers that are within 3 kilometer of the cell tower determined and output.

Then the next Radio Station in the Excel list becomes the next query criteria and it is compared to the Radio Station tower database and the radio station towers within the 3 kilometers distance are again output.

This repeats as each new query goes down the Excel Table list.

Any help is appreciated.

 

[The_Doc_Man]

To solve this problem, divide and conquer.

First, you can use an import operation to get data from an Excel spreadsheet to an Access table. Once you have the station list in the database, you are then on an All-Access solution. Linking to Excel is possible but it is far easier to import into Access.

Now comes the next part of interest... finding distance from tower A to tower B.

Question - what system is used for the tower coordinates? Are we talking latitude and longitude? And if so, to what precision? Hours? Minutes? Seconds? Something even smaller than these?

If I recall correctly, at the equator 1 degree is about 70 miles, so 1 minute of arc is about 70mi / 60 arc-minutes or about 1.66 mi per arc-minute. You also technically should reduce that "70" by the cosine of the latitude.

In any case, if you don't at least have the locations to the minute of arc, you have no hope of getting this accomplished. So what will you be using?

Another part of this is the required degree of precision. Do you need the distances to the km, or to a smaller unit?

Now, as to the comparison, there are many ways to skin this cat but the first question for the implied loop is, how many entries will you have to compare? Because it APPEARS that you have a very rare event... a case where a Cartesian join might actually be productive. But if you have too many stations, the time it takes to do the Cartesian JOIN could become prohibitive. The time required grows as *(n-1), which is pretty fast-growing.

To do this requires that you decide which method of distance computation you want to use because to do this efficiently you need a function that takes two records with location data and computes the distance for you. So to build the function you must choose whatever formula you need to use for that math.

If you are going "formal" then you will probably use one of three methods: Mercator projection, Lambert projection, or Cassini-Soldner projection. But there are other ways to do this as well. It doesn't matter (to us) which one you are going to use because no matter what, you are the one who will have to program and debug the math required for the distance computation.

 

[dd92251]

The math to calculate the distance is the simple part. I believe I have a code snippet that has that calculation covered.

Turning the Excel Spreadsheet into an Access table is something I know how to do, so that part is easy and I would do this manually. Whenever I get an Excel file, which usually has about 1000 cell sites, I would do the import.

Being a newbie to Access, my big issue is getting the coordinate data of the first record of the Cell Site table to become the query data for the calculation and output. Increment-ing down the table to the next record and then using that records' coordinate data for the next query is the part I'm not sure how to do.

The coordinate data is in Lat and Lon ( decimal format ) in both the Excel table and the Access database.

Any thoughts on how to automatically increment thru the Excel records and perform the query, automatically ?

I'm an old guy trying to learn new things

Thanks for any help you can provide.