Calculate differences between rows in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What I need to be able to do is to calculate the difference in a value
(Temperature) between each depth bin. Then to find the absolute value of the
biggest difference by Station Code and then to find out the Depth - 1 at
which that biggest difference occurred. I am sure this can be done, but I
can't figure out exactly how. Any help would be greatly appreciated.

Thanks, cam

Here is an example of my data:
Station Code Depth (m) Temperature (oC)
061906FS04 1 14.8279
061906FS04 2 14.8016
061906FS04 3 14.7952
061906FS04 4 14.7961
061906FS04 5 14.7955
061906FS07 1 13.4263
061906FS07 2 13.4332
061906FS07 3 13.4257
061906FS07 4 13.3969
061906FS07 5 13.3866
 
These queries will display the difference in temperature between depths and
give you the depth where the max temp diff is found.
CAM_DigData ---
SELECT DigData.[Station Code], DigData.[Depth (m)], DigData_1.[Depth (m)],
DigData.[Temperature (oC)], DigData_1.[Temperature (oC)],
[DigData].[Temperature (oC)]-[DigData_1].[Temperature (oC)] AS [Temp Diff]
FROM DigData INNER JOIN DigData AS DigData_1 ON DigData.[Station Code] =
DigData_1.[Station Code]
WHERE (((DigData_1.[Depth (m)])=[DigData].[Depth (m)]+1));

CAM_DigMaxTempDiff ---
SELECT CAM_DigData.[Station Code], Max(CAM_DigData.[Temp Diff]) AS
[MaxOfTemp Diff]
FROM CAM_DigData
GROUP BY CAM_DigData.[Station Code];

CAM_DigMaxTempWithDepth ---
SELECT CAM_DigData.[Station Code], CAM_DigData.DigData.[Depth (m)],
CAM_DigData.DigData_1.[Depth (m)], CAM_DigData.DigData.[Temperature (oC)],
CAM_DigData.DigData_1.[Temperature (oC)]
FROM CAM_DigData INNER JOIN CAM_DigMaxTempDiff ON (CAM_DigData.[Temp Diff] =
CAM_DigMaxTempDiff.[MaxOfTemp Diff]) AND (CAM_DigData.[Station Code] =
CAM_DigMaxTempDiff.[Station Code]);
 
Karl,
Just had to make one minor adjustement since I wanted the depth shallower
than max difference instead of deeper, and it works like a charm.

THANKS!
cam

KARL DEWEY said:
These queries will display the difference in temperature between depths and
give you the depth where the max temp diff is found.
CAM_DigData ---
SELECT DigData.[Station Code], DigData.[Depth (m)], DigData_1.[Depth (m)],
DigData.[Temperature (oC)], DigData_1.[Temperature (oC)],
[DigData].[Temperature (oC)]-[DigData_1].[Temperature (oC)] AS [Temp Diff]
FROM DigData INNER JOIN DigData AS DigData_1 ON DigData.[Station Code] =
DigData_1.[Station Code]
WHERE (((DigData_1.[Depth (m)])=[DigData].[Depth (m)]+1));

CAM_DigMaxTempDiff ---
SELECT CAM_DigData.[Station Code], Max(CAM_DigData.[Temp Diff]) AS
[MaxOfTemp Diff]
FROM CAM_DigData
GROUP BY CAM_DigData.[Station Code];

CAM_DigMaxTempWithDepth ---
SELECT CAM_DigData.[Station Code], CAM_DigData.DigData.[Depth (m)],
CAM_DigData.DigData_1.[Depth (m)], CAM_DigData.DigData.[Temperature (oC)],
CAM_DigData.DigData_1.[Temperature (oC)]
FROM CAM_DigData INNER JOIN CAM_DigMaxTempDiff ON (CAM_DigData.[Temp Diff] =
CAM_DigMaxTempDiff.[MaxOfTemp Diff]) AND (CAM_DigData.[Station Code] =
CAM_DigMaxTempDiff.[Station Code]);


CAM said:
What I need to be able to do is to calculate the difference in a value
(Temperature) between each depth bin. Then to find the absolute value of the
biggest difference by Station Code and then to find out the Depth - 1 at
which that biggest difference occurred. I am sure this can be done, but I
can't figure out exactly how. Any help would be greatly appreciated.

Thanks, cam

Here is an example of my data:
Station Code Depth (m) Temperature (oC)
061906FS04 1 14.8279
061906FS04 2 14.8016
061906FS04 3 14.7952
061906FS04 4 14.7961
061906FS04 5 14.7955
061906FS07 1 13.4263
061906FS07 2 13.4332
061906FS07 3 13.4257
061906FS07 4 13.3969
061906FS07 5 13.3866
Click to expand...
Click to expand...
 
Back
Top