Need MS Access update query to find/insert all towns within x miles of record

Have a look here
https://www.experts-exchange.com/questions/21942686/Zip-Code-Radius-for-MS-SQL.html

Thanks but neither of these includes what I am after. One is MS SQL and the other is a VBA page with similar functions.

I am after an MS Access update query to locate records within a specific distance.

Thanks,

What you're asking is to add x number of records to an existing table that has County, Lat and Long.

Today, if you have

County           Lat             Long
ABC              123              456

And you want to add a field

County           Lat             Long     Local_Towns
ABC              123              456

There could be hundreds added to this table. And that is a bad design.

Do you have an ID field in this table?

ID    County           Lat             Long
1        ABC              123              456

if so, you want to create a new table....not add to this one.

Create a new table with a FK to your existing table's ID field. Create your records there. Otherwise you're going to end up with duplicated records in 3 of your 4 fields for perhaps thousands of records.

Just my .02
J

<<I am after an MS Access update query to locate records within a specific distance.>>

  Yes it is.  The page I posted has a function called DISTANCEsm(), for distance in statute miles.

  For your update, you'll need to execute a function that would loop through the zip table and for each record, execute a query which returns all towns within x miles (using the Distance function), then update your field with the names of those towns.

JimD.

BTW, this doesn't sound like a great idea.  If the X distance is subject to change, your town list would be invalid.  For example, if I'm in the country, it might not be unreasonable to have a 30 mile radius, but if I'm in a city, 3 miles might be more then ehough.

Building on what Jim said, you'll probably want to output this as a report vs storing these calculated lists as part of your permanent database. Create a form where you can input on the fly things like your radius, and perhaps even a town. So you can use it as an On-Demand sort of thing. This flexibility will keep you from having to re-code or rebuild your query each time someone needs to know something that you can't provide with stored data.

Jim

Apologies, I am pretty proficient with Queries but functions are new to me. I have created the Distancesm and DegARCCOS functions and seem to be nearly there.

I run a query  with a field as:
Expr1: Distancesm(-35.378478,23.283579,87.2963,43.3121)

It halts at the one line in DegARCCOS fucntion, below saying"Method or Data Member not found". I understand that the .Acos mthod doesnt exist. Where do I obtain/create it?

DegARCCOS = DEGS * Application.Acos(x)

Jeff

I completely agree with your comments and the others in terms of this being bad database design. This is a one off for an export to a wiki. I need to creata the wiki from this output at which point the field will be removed.

The X miles is already setup for different state/county combos which are designated as rural/urban and will automatically adjust.

Thanks,

cos(x) will do

<<It halts at the one line in DegARCCOS fucntion, below saying"Method or Data Member not found". I understand that the .Acos mthod doesnt exist. Where do I obtain/create it?>>

  Sorry, my mistake.  I didn't notice that the code posted was written in VBA for Excel.  Those functions are not available in the default VBA setup for Access.  You probably just need to add a reference to the Excel type lib.

  Open a module, click tools/references, and check the box for "Microsoft Excel 11.0 Object Lib".  If that doesn't work, we'll need to find code to place those functions directly into Access.

JimD

No, the cos(x) suggestion has me there. It works fine! :-)

One oddity is that when I have the identical long/lat for start and end point it gives me an answer of 2126.98629184??? I would have expected '0'.

Paul

hmmm...change that. The answer varies depending on what I feed it.  ???

Expr1: Distancesm(44,44,44,44)  = 2126.98629184

Expr1: Distancesm(44,44,44,44) = 2137.33396648

sorry, first one should be Distancesm(0,0,0,0)

<<No, the cos(x) suggestion has me there. It works fine! :-)>>

  cos(x) and acos(x) are different functions.  The first is cosine, the other arc cosine.

JimD.

Jim

Access doesnt recognize Acos(x). I will try to find out what it is and how to implement it. I do think that once I have that cracked it will work fine.

Paul

Found it!

http://nmml.afsc.noaa.gov/Accessibility/AccessExcelGeoFunctions.html

Function arccos(x)
arccos = Atn(-x / Sqr(-x * x + 1)) + Pi / 2
End Function

Thanks for being patient with a less than experienced user!

Paul

<<Thanks for being patient with a less than experienced user!>>

  Not a problem. I'm sorry I didn't find something a little more complete first time around.

JimD.