Help with lat longs GREATLY appreciated!

[dance]

I would really appreciate some help with this:

I have two database tables:
-table one contains c.100 decimal lat/longs and a temperature.
-table two contains lat/longs for the whole Earth (lots!) and a temperature for each coordinate.

I want to query table one against table two and return a report which:

-returns a figure which is an average of the temperature four coordinates .5 of a degree N,S,E+W for each test site.
-compares the test site temp and the new averaged temp against each other

I'm told this should be quite straightforward but I can't figure out how to do this. I'd love some guidance.

Thanks!

P.S. I'm not worried about points near the poles being closer than those elsewhere at the moment (unless there is an easy way of dealing with this?)

 

[dance]

bump? can anyone help?

I don't think specific knowledge of lat longs is necessary if that's putting anyone off?

 

[neileg]

You need to create a dataset that contains the four points adjacent to your site. You could use, VBA or you could create four select queries that have a calculated field for each E,S,W,N variation and union them together. Join this dataset with your second table on the Lat/Long in an aggregate query to calculate the average.

 

[dance]

I need to return a report containing corresponding data for each of the c.100 sites in table one. Would you be willing to help me with that? If you are, contact me on access-programmers@trashmail.net and we can discuss recompense for your work.

 

[neileg]

Thanks for the offer but my employer wouldn't allow that. You'll get plenty of good advice on here for free.

Having another think, don't you need 5 points - the co-incident point and the 4 variations?

 

[dance]

Yep, I would need the original point and the .5 N,S,E,W variations on it.

I've had some really good advice elsewhere but I can't implement it because my Access skill level isn't good enough. Here, for example, someone pretty much hands it to me on a plate, but I can't work with it because I can't get the basics sorted out. Perhaps you could help there? I would really appreciate it.

 

[CraigDolphin]

Dance,

I have used the Dlookup function and a little elementary math to achieve what I think you want; both in form view and in a query.

have a look at this example db. Assuming that all your lat-longs are accurate to the same precision (decimal places) and that you will have all four lat-longs co-ordinates available in your lookup table for each site, then this method would work. If not, then you would need to work on rounding to the coarsest resolution/lowest precision that you have available in your tables.

Hope it gets you started on the right path.

Cheers!

 

[dance]

Craig, I really appreciate this - whether it works or not!

I'll test this asap and get back to you, if that's okay?

 

[CraigDolphin]

Glad to help and interested to know how it turns out.

 

[dance]

Well, I pasted in my data to replace the dummy data provided and I think it hung. I don't know for sure because whilst I left it drawing and computing, the computer went to sleep. So I took out all but 10 of my test sites (but leaving the huge world data table intact) and tried again. This time I know it definitely hung.

I'll try again in the morning, though, and let you know how that goes.

Thanks again!

 

[CraigDolphin]

Maybe your world table is too big for Access to handle with Dlookup. Maybe you could use a make table query to create a subset of lookup data limited to data within the range of degrees in your 100 sites.

That is, if all your sites are between 45 and 60 degrees lat, and 100 and 120 degrees long, then create a new table restricted to observations in those latitudes (+/-1 0.5 degrees), and try the dlookup on the new table.

Also, remember that Dlookup will require an exact match in each field. If your world-lookup lat/long fields store 4 decimal places, and your site fields store 3, or 5 decimal places, then you will have mismatches. If that is the case, then you may need to round your higher precision site lat number, before adding or subtracting the half degreee and performing the dlookup function.

If you still get stuck, see if you can post your db up with enough data to test (but not too much to upload) and I or someone else can look at it tomorrow.