Date Change Notification Query

[McArthurGDM]

Assume that I have a list of projects and I track the status of these projects quarter to quarter. Associated with the projects is a project start and end date. I want to make note of when a start date (or end date) changes given the prior start date (or end date) changes. The query should produce results like the table below illustrates.

Project	Quarter	Start Date	End Date	Start Change?	End Change?
1	Q1	1/1/2015	3/1/2015
1	Q2	1/1/2015	3/1/2015
1	Q3	3/1/2015	5/1/2015	Yes	Yes
1	Q4	3/1/2015	5/1/2015
2	Q1	4/1/2015	6/1/2015
2	Q2	4/1/2015	7/1/2015		Yes
2	Q3	4/1/2015	7/1/2015
2	Q4	4/1/2015	8/1/2015		Yes
3	Q1	1/1/2015	5/1/2015
3	Q2	2/1/2015	5/1/2015	Yes
3	Q3	2/1/2015	5/1/2015
3	Q4	3/1/2015	5/1/2015	Yes
4	Q1	1/1/2015	3/1/2015
4	Q2	1/1/2015	4/1/2015		Yes
4	Q3	1/1/2015	4/1/2015
4	Q4	1/1/2015	4/1/2015

How can I create this query?

Thank you!

[June7]

Consider:

SELECT Table2.Project, Table2.Quarter, Table2.StartDate, Table2.EndDate, IIf(DMax("StartDate","Table2","Project=" & [Project] & " AND Quarter<'" & [Quarter] & "'")<>[StartDate],True,Null) AS StartChange, IIf(DMax("EndDate","Table2","Project=" & [Project] & " AND Quarter<'" & [Quarter] & "'")<>[EndDate],True,Null) AS EndChange
FROM Table2;


Be aware domain aggregates can perform slowly in queries.

[McArthurGDM]

I'm not really sure why, but I am getting errors when the query computes: here is what I put..

IIf(DMax("Start_Date","Info","Initiative_ID=" & [Info].[Initiative_ID] & " AND CalcSort2< " & [CalcSort2] & "")<>[Start_Date],True,Null)

[June7]

Exactly what error?

[McArthurGDM]

#Error

In every cell.

[June7]

Post the full SQL statement of the query.

I seem to remember testing this and it worked for me.

[McArthurGDM]

This has the original names of the table/fields in it, but the idea is the same. It's important to note that CalcSort to is a calculated field, so that might have something to do with the error. I gave the quarters those values because I have to also consider monthly values.

SELECT Info.Initiative_ID, Main.Period, Info.Start_Date, Info.End_Date, IIf([Period]="Q1",3.5,IIf([Period]="Q2",6.5,IIf([Period]="Q3",9.5,IIf([Period]="Q4",12.5,[CalcSort])))) AS CalcSort2, IIf(DMax("Start_Date","Info","Initiative_ID=" & [Info].[Initiative_ID] & " AND CalcSort2<'" & [CalcSort2] & "'")<>[Start_Date],True,False) AS StartChange
FROM Main INNER JOIN (Info INNER JOIN Initiative ON Info.Initiative_ID = Initiative.Initiative_ID) ON (Main.Main_ID = Initiative.Main_ID) AND (Main.Main_ID = Info.Main_ID);

[rpeare]

I'm not a fan of using domain functions in queries. Assuming your table is named

tblTest and has the fields Project, Quarter, StartDate and EndDate with the appropriate data types try this:

SELECT tblTest.Project, tblTest.Quarter, tblTest.StartDate, tblTest.EndDate, Max(IIf([tbltest_1]![quarter]<[tbltest]![quarter],[tbltest_1]![startdate],Null)) AS PrevStart, Max(IIf([tbltest_1]![quarter]<[tbltest]![quarter],[tbltest_1]![enddate],Null)) AS PrevEnd, IIf(IsNull(Max(IIf([tbltest_1]![quarter]<[tbltest]![quarter],[tbltest_1]![startdate],Null))),Null,IIf(Max(IIf([tbltest_1]![quarter]<[tbltest]![quarter],[tbltest_1]![startdate],Null))<>[tbltest]![startdate],"Yes",Null)) AS IndicatorStart, IIf(IsNull(Max(IIf([tbltest_1]![quarter]<[tbltest]![quarter],[tbltest_1]![enddate],Null))),Null,IIf(Max(IIf([tbltest_1]![quarter]<[tbltest]![quarter],[tbltest_1]![enddate],Null))<>[tbltest]![enddate],"Yes",Null)) AS IndicatorEnd
FROM tblTest LEFT JOIN tblTest AS tblTest_1 ON tblTest.Project = tblTest_1.Project
GROUP BY tblTest.Project, tblTest.Quarter, tblTest.StartDate, tblTest.EndDate
ORDER BY tblTest.Project, tblTest.Quarter;

I left a couple of extra fields in there to show you the calculation of the 'previous' start and end dates which I plugged into the indicator columns.

[June7]

First example does not show apostrophe delimiters for CalcSort2 but the query statement does. If CalcSort2 is a number value, do not use apostrophe delimiters.

[McArthurGDM]

What's tblTest_1?

[rpeare]

the same table added a second time

[McArthurGDM]

How would i rewrite this if the Quarter is a calculated field within my Query that I call CalcSort2? That is, I have an expression where I called Q1=1, Q2=2, etc... So the field is not in another table, but within the query itself.

[McArthurGDM]

Thanks. Never mind about the rewrite part. I just made it so that's it's calculated in the table and not the query so that fixes that.

[McArthurGDM]

I don't know, I can't get anything to work. This is just to calculate the start date.

The whole SQL IS:

SELECT Info.Initiative_ID, Main.CalcSort, Info.Start_Date, Info.End_Date, IIf(DMax("Start_Date","Info","Initiative_ID=" & [Info].[Initiative_ID] & " AND CalcSort<" & [Main].[CalcSort])<>[Start_Date],True,False) AS StartDate
FROM Main INNER JOIN (Info INNER JOIN Initiative ON Info.Initiative_ID = Initiative.Initiative_ID) ON (Main.Main_ID = Initiative.Main_ID) AND (Main.Main_ID = Info.Main_ID);

I appreciate the help.

[rpeare]

Did you try the query I gave you.

Use the data you gave us, just make a table called tblTest with fields named the same way I said and it will work.