Help Writing Date Change SQL Code

[McArthurGDM]

So,



I have an excel spreadsheet here that I want to be able to replicate in SQL. That is, to create the End_Date_Change and Start_Date_Change fields.
Essentially, I have an Initiative field that lists initiatives ("projects", really). The status of these initiatives is given to me every reporting period, or "Period_Date". Associated with these Initiatives are Start Dates and End Dates of the initiative, along with a "Status" of the initiative field.

A change in End/Start date implies that the particular initiative start/end date has changed. For the End Date change, the criteria is that a change isn't considered if the initiative status is "Complete", and the criteria for the Start Date Change is that the initiative status is considered "Not Started". If the criteria is met, then I just marked the fields with a "YES".

I wrote excel code to help illustrate:

For the End_Date_Change field-

=IF(A3=A2,IF(AND(D3<>D2, E3 <> "Complete"),"YES",""),"")

For the Start_Date_Change field-

=IF(B3=B2,IF(AND(C3<>C2, E3 = "Not Started"),"YES",""),"")

ROW	Initiative	Period Date	Start_Date	End_Date	Status	Submission	End_Date_Change	Start_Date_Change
1	Initiative 1	1/1/2015	4/1/2015	8/31/2015	Not Started	1/15/2015
2	Initiative 1	2/1/2015	5/1/2015	8/31/2015	Not Started	3/16/2015		YES
3	Initiative 1	3/1/2015	5/1/2015	8/31/2015	In Process	4/15/2015
4	Initiative 1	4/1/2015	5/1/2015	8/31/2015	In Process	5/15/2015
5	Initiative 1	5/1/2015	5/1/2015	9/30/2015	In Process	2/15/2015	YES
6	Initiative 1	6/1/2015	5/1/2015	10/1/2015	Complete	6/15/2015
7	Initiative 2	1/1/2015	3/1/2015	10/1/2015	Not Started	1/15/2015
8	Initiative 2	2/1/2015	4/1/2015	10/1/2015	In Process	3/14/2015
9	Initiative 2	3/1/2015	4/1/2015	11/1/2015	In Process	2/15/2015	YES
10	Initiative 3	1/1/2015	3/1/2015	10/1/2015	Not Started	1/15/2015
11	Initiative 3	2/1/2015	4/1/2015	10/5/2015	Not Started	3/14/2015	YES	YES
12	Initiative 3	3/1/2015	4/1/2015	11/1/2015	In Process	2/17/2015	YES

Any help is appreciate with writing this code!

Thanks,

[ranman256]

This makes no sense..
IF(A3=A2

A col =[ROW], ....THEY WILL NEVER EQUAL!

[June7]

This is each record pulling a value for comparison from another record of same table. Requires nested subquery. Review: http://allenbrowne.com/subquery-01.html#AnotherRecord

Or domain aggregate function (DLookup, DMax, DMin, etc).

[McArthurGDM]

Ranman256, Column A begins with the Initiative field - sorry for that. I tried to exemplify what the rows were for the first column.

[ranman256]

Unlike Excel, Access cannot compare 'the next row' (like A2=A3).
you must use a key or something that will individualize the record... [ key]=2 vs [key]=3