Date Change Notification Query

[McArthurGDM]

The problem with that code seems to be the fact that the Quarter information comes from a table called "Main" and the Project information from a table called "Info". Since they come from two different tables - which are linked via "MainID", can I make duplicates of each in the same manner and have it work?

[McArthurGDM]

Here is what the table structure looks like.

I am concerned with the fields Info.Initiative_ID (Projects), Info.Start_Date, Info.End_Date, Main.CalcSort (I assigned numerical values for the period).

The code you gave me dosen't work because you, I think, assumed that the Projects field was on the same table as the CalcSort field.

Thanks,

[June7]

Appears to be circular reference, review: http://www.codeproject.com/Articles/...atabase-Design

Why are there no primary key fields defined?

[McArthurGDM]

That's correct. Take away the link from the Main table to the Initiative table and now it gives me the correct amount. Still the same issue with the code, though. Thanks for pointing that out.

[rpeare]

Which table has the 'main' date, the date for which you want to find the maximum date lesser than? What is the field name of that date field?

Which table has the 'sub' date, the date for which you want to find the maximum value that is also under the 'main' record? What is the field name for that date field?

Can either table have activity for the same Main_ID on the same day or is there only one record per day per Main_ID.

[McArthurGDM]

I changed a few things around from the last picture I posted.

I should probably explain better what all this is.
Assume you have a list of projects (Initiative_ID's) and progress on those projects is submitted every period (month/quarter - which I designate by CalcSort). The person submitting information about a project specifies that they believe the project has a Start_Date of whenever and an End_Date of whenever. Over each period they submit the status of that project, the Start_Date and End_Date can change based on what they believe could happen. But I want to note, if over a period, they changed the Start_Date or End_Date.

The query should really only be dependent on 4 fields. The initiative_ID field which comes from the Initiative table, the CalcSort field which comes from the Main table (these are just numbers that I allocated to months, 1-12, and quarters, 1-4, instead of a text field), the Start_Date and End_Date fields come from the Info table.

Here is an example (subset) of the output and the desired highlight. I want a field to indicate when a Start_Date or End_Date has changed over time given a particular Initiative_ID.

Initiative_ID	CalcSort	Start_Date	End_Date
15-US CO-33	3	9/1/2015	10/31/2015
15-US CO-33	3	9/1/2015	10/31/2015
15-US CO-34	0	1/1/2015	3/31/2015
15-US CO-34	1	1/1/2015	5/20/2015
15-US CO-34	2	1/1/2015	6/15/2015
15-US CO-34	3	1/1/2015	6/15/2015
15-US CO-34	3	1/1/2015	6/15/2015
15-US CO-35	0	2/1/2015	12/31/2015
15-US CO-35	1	2/1/2015	12/31/2015
15-US CO-35	2	2/1/2015	12/31/2015

Let me know if that makes sense or not,

Thanks!