Calculation on "time 1" and "time 2" data

[judehamilton]

Hello, all--I have a large collection of water quality data collected every 30 minutes from 1999 to 2006. I have attached a truncated version of one of my tables so you can see what I am working with.

I need to conduct some calculations on these "DO_pct" and "DO_mgl" data at overlapping, consecutive data points (which I am calling "time 1" and "time 2"): i.e., from time 1 (line 1) and time 2 (line 2); time 1 (line 2) and time 2 (line 3); and so on. I have the calculated field ready to go, but am somehow confounded as to how to set up a query or series of queries that will include "time 1" and "time 2" fields for both the "DateTimeStamp" (mm/dd/yyyy h:nn) and the DO values in the same line so I can perform the calculation. I am thinking of an output such as the following:

Time1 Time2 T1_DO_pct T2_DO_pct
06/03/1999 18:00 06/03/1999 18:30 116.8 121
06/03/1999 18:30 06/03/1999 19:00 121 120.4
06/03/1999 19:00 06/03/1999 19:30 120.4 119.7


This seems to me like it should be a simple operation, but I have not been successful thus far...my output has included only about 2/3 of the "time 1/time 2" values. Hopefully someone can enlighten me as to how to set this up.

Thank you in advance for any input you can provide!

Judy

 

[emorris1000]

I *think* I was just fighting this problem.

So you have table 1 which is:

Time1 Value1
1____x
2____x
3____x
4____x
5____x
6____x


and table 2 which is

Time1 Value 2
2____x
4____x
6____x


and what you want is
Time Value1 Value2
1______x______null
2______x______x
3______x______null
4______x______x
5______x______null
6______x______x


To do this you need to perform a full outer join of the time values from each table (to make Query1) and then perform an inner join of the tables Table1 and Table2 to Query1 using Time.

I did this in the following link (can't post links)
"w.w.w.access-programmers.co.uk/forums/showthread.php?t=205704"

 

[Brianwarnock]

There is only one table.

You can do this based on the ID provided all entries are done in the correct order , or bettter on the timestamp.

SELECT Valino.DateTimeStamp, Valino_1.DateTimeStamp, Valino.DO_pct, Valino_1.DO_pct
FROM Valino, Valino AS Valino_1
WHERE Datediff("n",[valino].[DateTimeStamp],Valino_1.DateTimeStamp)=30;

for completeness

SELECT Valino.DateTimeStamp, Valino_1.DateTimeStamp, Valino.DO_pct, Valino_1.DO_pct
FROM Valino, Valino AS Valino_1
WHERE (((Valino_1.ID)=[valino].[id]-1));

Brian

 

[judehamilton]

Thank you both for your replies...and Brian, as always, you rock.