Avatar of pcoghlan
pcoghlan

asked on 

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

I have an MS Access database in which there is a table of 50k+ US towns which include the County, Long and Lat.

I need to run an update query to insert into a new field [Local_Towns] all other towns in the table located within x miles of each town.

Can anyone advise on the Query code?

Thanks,
Paul


Microsoft AccessSQL

Avatar of undefined
Last Comment
Jim Dettman (EE MVE)
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of pcoghlan
pcoghlan

ASKER

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,
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

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.
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

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.
Avatar of pcoghlan
pcoghlan

ASKER

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)
Avatar of pcoghlan
pcoghlan

ASKER

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,
Avatar of jefftwilley
jefftwilley
Flag of United States of America image

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
Avatar of pcoghlan
pcoghlan

ASKER

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
Avatar of pcoghlan
pcoghlan

ASKER

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

Avatar of pcoghlan
pcoghlan

ASKER

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.
Avatar of pcoghlan
pcoghlan

ASKER

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
Avatar of pcoghlan
pcoghlan

ASKER

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.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo