Please help with an Access/Database question!

November 18, 2006 7:03 AM

Help with MS Access needed please - I'm told it should be a fairly simple query to conduct, but I don't think I'm upto the task solo. Can the hive mind help?

I got some brilliant answers the last time I asked for help on this topic, but ultimately the answers were either pitched at a higher skill level than mine with Access or were not directly relevent though still gratefully appreciated.

I'll try again and would really appreciate some help.

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.
-(optional!)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. Those who point out the problems with .5 of a degree being closer at the poles are of course correct, but I hope to worry about that later.

posted by dance to Computers & Internet (12 answers total)

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

Last time, I tried to suggest problems with your algorithm; this time I'll just implement it as stated. Let's break it down into discrete simple steps:

1) "four coordinates .5 of a degree N,S,E+W for each test site"

Ok, first, let's write a view that transforms each test coordinate into four coordinates you want

create view test_transform_1 as
select *, lat + .5 as north_lat , lon as north_lon, lat -.5 as south_lat, lon as south_lon, lat as east_lat, lon + .5 as east_lon, lat as west_lat, lon - .5 as west_lon

OK, now if any original lat was 90 or lon was 180 and we added to it, our sum is invalid, so we fix that here:

create view test_transform_2 as
select lat, lon, temp,
case when north_lat > 90 then 89.5 else north_lat end as north_lat,
case when north_lat > 90 then -north_lon else north_lon end as north_lon
case when south_lat < -90 then -89.5 else south_lat end as south_lat, br> case when south_lat < -90 then -south_lon else south_lon end as south_lon,br> east_lat,
case when east_lon > 180 then -179.5 else east_lon end as east_lon,
west_lat,
case when west_lon < -180 then 179.5 else west_lon end as west_lon,br>

Ok, I think that's the correct fix, but it's up to you to test it. Now, we can use test_transform_2 to join to the whole earth sample, and return the temps.

But I'm out of cigarettes and some asshole is practicing his drum sets (or something, so you'll have to handle that yourself).
posted by orthogonality at 7:32 AM on November 18, 2006

Response by poster: orthogonality, thank you! I realise my logic appears flawed, but I hope to improve on the algorithm once I (we!) have got at least a basic working model thrashed out. I'll test it and report back.
posted by dance at 7:37 AM on November 18, 2006

Hey dance, make answerers life easier, and post a sample (just a row or two) from each table, Ok?
posted by orthogonality at 7:39 AM on November 18, 2006

Response by poster: and one more thing...do you IRC or Jabber? It might be useful to briefly talk about some of this in real time.
posted by dance at 7:40 AM on November 18, 2006

Response by poster: Re: sample data

Sure, I'll do that right away.

Thanks
posted by dance at 7:41 AM on November 18, 2006

dance writes "and one more thing...do you IRC or Jabber? It might be useful to briefly talk about some of this in real time."


Well, I IRC (I don't Jabber, whatever that is), but I charge an hourly rate for that (with a discount for mefites). If you want to go that route, email me and we can discuss.
posted by orthogonality at 7:48 AM on November 18, 2006

Response by poster: orthogonality - it may come to that, but I'd love to try goold old MetaFilter first!

Right - now to post the sample data..
posted by dance at 8:03 AM on November 18, 2006

Response by poster: Okay, you should be able to find it at:

http://tinyurl.com/uwgyp

It's exported from Access > Excel > Web page

I hope you can view it okay as I expect Excel isn't particularly good at exports.

The code you gave me above - should I be entering that into visual basic or as a query somehow? That was my problem with implementing your previous solution - I didn't know where to begin, I think once I know that it'll be easier!
posted by dance at 9:55 AM on November 18, 2006

Dance: Just pay the man. He has already answered your question twice with the details. :)
posted by SirStan at 4:30 PM on November 18, 2006

Response by poster: SirStan: Right-O.

Anyone have any ideas about the basics? I'm sure Orthogonality's answer is brilliant - but what do I do with it?
posted by dance at 4:18 AM on November 19, 2006

Response by poster: Ah - looks like I need to use JET...I'll look into it.
posted by dance at 4:34 AM on November 19, 2006

It looks like the syntax recommended above is actually for SQL Server. Access does not support CREATE VIEW or CASE.
posted by owhydididoit at 10:54 AM on January 26, 2007