February 26, 2009 at 12:13 pm
Hello -
I have rain data for a weather station stored in Access. I have "weighing bucket" where every hour I get the average weight of that bucket (over the last hour) - call it "R". If that goes down, we can assume evaporation, if it goes up, the amount of rain, in inches is (R at Time T)-(R at Time (T-1)). I want to write a query where if (R at Time T) < (R at Time (T-1)), then Rcalc = 0, otherwise, Rcalc = [(R at Time T) - (R at Time (T-1))]
My data might look something like this:
10/28/2006 3:000.1
10/28/2006 4:000.09
10/28/2006 5:000.09
10/28/2006 6:000.09
10/28/2006 7:000.09
10/28/2006 8:000.09
10/28/2006 9:000.1
10/28/2006 10:000.12
10/28/2006 11:000.13
10/28/2006 12:000.14
10/28/2006 13:000.13
Where for the first 6 rows, the Rcalc would be 0, then we have an event, lasting 4 hours, where you have 0.01, 0.02, 0.01 and 0.01 inches of rain per hour, then the last amount would be zero again.
I thought about doing a row counter but don't know how to do that in Access.
Any clever ideas out there?
Thanks in advance,
Sanjay
February 27, 2009 at 6:49 am
if (R at Time T) < (R at Time (T-1)), then Rcalc = 0, otherwise, Rcalc = [(R at Time T) - (R at Time (T-1))]
CREATE TABLE MyWeather
(T DATETIME NOT NULL PRIMARY KEY,
R DECIMAL(10, 2) NOT NULL);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 03:00:00', 0.1);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 04:00:00', 0.09);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 05:00:00', 0.09);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 06:00:00', 0.09);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 07:00:00', 0.09);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 08:00:00', 0.09);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 09:00:00', 0.1);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 10:00:00', 0.12);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 11:00:00', 0.13);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 12:00:00', 0.14);
INSERT INTO MyWeather (T, R) VALUES ('2006-10-28 13:00:00', 0.13);
-- using a calculated join and a derived table
SELECT IIF(R1 < R2, 0, R1-R2) AS RCALC, X.*
FROM(SELECT W1.T AS T1, W1.R AS R1, W2.T AS T2, W2.R AS R2
FROM MyWeather AS W1
INNER JOIN MyWeather AS W2
ON W1.T = DATEADD("h",1, W2.T)) AS X;
brgds
Philipp Post
brgds
Philipp Post
February 27, 2009 at 9:31 am
Philipp -
Thanks a ton - that works really well - I'll have to add that to my arsenal....
Best,
SA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply