Would like to display elevation changes for instruments that are surveyed on
an annual basis. Also, would like to display total change through time.

InstID Survey Date Elevation
4 05/09/2001 176.245
4 07/11/2002 176.166
4 04/15/2003 176.170

I modified an SQL statement from a previous reply by Jamie Collins on
10/28/2004 as follows (table name is Test):

SELECT T2.InstID, T2.SurveyDate AS Expr1, [T2].[Elevation]-IIf((SELECT
T1.Elevation FROM Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate)) Is Null,0,(SELECT T1.Elevation FROM
Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate))) AS new_amount
FROM Test AS T2
ORDER BY T2.SurveyDate;

Results
InstID Survey Date new_amount
4 05/09/2001 176.245
4 07/11/2002 -0.079
4 04/15/2003 0.004

This is very close to what I would like to accomplish. How do I modify the
SQL to calculate the new_amount for the first Survey Date to be NULL or 0 so
that I may sum the new_amount for the period of record?

Thanks in advance for any assistance,
Chris

Try this:

SELECT T2.InstID, T2.SurveyDate AS Expr1, [T2].[Elevation]-IIf((SELECT
T1.Elevation FROM Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate)) Is Null,0,(SELECT T1.Elevation FROM
Test T1 WHERE T1.SurveyDate = Nz((SELECT MAX(SurveyDate) FROM
Test WHERE SurveyDate<T2.SurveyDate),0))) AS new_amount
FROM Test AS T2
ORDER BY T2.SurveyDate;


--

Ken Snell
<MS ACCESS MVP>

"csf" <(E-Mail Removed)> wrote in message
news:717AE0F6-6EC0-4975-AFDF-(E-Mail Removed)...
> Would like to display elevation changes for instruments that are surveyed
> on
> an annual basis. Also, would like to display total change through time.
>
> InstID Survey Date Elevation
> 4 05/09/2001 176.245
> 4 07/11/2002 176.166
> 4 04/15/2003 176.170
>
> I modified an SQL statement from a previous reply by Jamie Collins on
> 10/28/2004 as follows (table name is Test):
>
> SELECT T2.InstID, T2.SurveyDate AS Expr1, [T2].[Elevation]-IIf((SELECT
> T1.Elevation FROM Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate)
> FROM
> Test WHERE SurveyDate<T2.SurveyDate)) Is Null,0,(SELECT T1.Elevation FROM
> Test T1 WHERE T1.SurveyDate = (SELECT MAX(SurveyDate) FROM
> Test WHERE SurveyDate<T2.SurveyDate))) AS new_amount
> FROM Test AS T2
> ORDER BY T2.SurveyDate;
>
> Results
> InstID Survey Date new_amount
> 4 05/09/2001 176.245
> 4 07/11/2002 -0.079
> 4 04/15/2003 0.004
>
> This is very close to what I would like to accomplish. How do I modify
> the
> SQL to calculate the new_amount for the first Survey Date to be NULL or 0
> so
> that I may sum the new_amount for the period of record?
>
> Thanks in advance for any assistance,
> Chris