I'm trying to write a query to find the closest coordinate in a table named WeatherData and return related fields from two tables. The geographic coordinate inputs are from the Locations table.
The WeatherData table contains fields,
ID (auto generated primary key),
Date (String DD/MM/YYY),
Latitude (Double 3 decimal places),
Longitude (Double 2 decimal places),
The query will need to iterate through the Locations table described below and find the closest Latitude and Longitude in the WeatherData table, returning Date, Latitude, Longitude, Factor (WeatherData table), and LocationID, Name (Locations table).
LocationID (Number Integer)
Name (String short text)
Latitude (Double 2 decimal places)
Longitude (Double 3 decimal places)
I'm not sure where to start. I have found an example unfortunately its not MSAccess and couldn't translate it.
Any help advice will be greatly appreciated!