Query to determine if In-process jobs are behind schedule

[jmaher196]

Hello,
I am in the process of completley reworking an Access database that my officeuses to track progress on ongoing research projects. As part of this effort, I have created a form with a section where project milestone can be recorded. Each project can have up to 5 milestones, and each milestone has several sections.
These sections are:

Estimated Start Date
Actual start date
Estimated End Date
Actual End Date

On the form, all of these are text boxes in the Date/Time format with simple date being used. The form then populates the relevant fields on the table.

All of the estimated dates are entered when the project is originally entered into the system, and the actual dates are entered as those milestones are completed. I would like to create a query that will only pull out the records that have an estimated completion date before the current date, based on the most recent milestone.

For example, for milestone 1 the estimated start date is 03/22/2012, the actual start date is 03/24/2012, the estimated completion date is 04/12/2012, and the actual completion date is 04/12/2012 so milestone 1 has been completed. For milestone 2 the dates are 04/14/2012 for estimated start, 04/15/2012 for actual start, and estimated finish is 05/22/2012. Milestone 2 is not complete yet so it has no actual end date.

How do I set up the query so it shows that the project is behind schedule since the estimated end date is 05/22/2012, but today's date is already 6/4/2012? I want it to pull all records that fit this condition.

Sorry for the long description. Thanks for your help!

[TheSmileyCoder]

In your query, you write 2 criterias. For ActualEndData you write the criteria "Is Null". This will ensure you only get the milestones that have not been completed. For EstimatedEndDate you write the criteria "<Date()" which will give you the projects that are behind schedule.

The combination of these two should fullfill your needs.

[jmaher196]

@TheSmileyCoder
I have tried to do that, but it seems to only work if all of the milestones have dates filled in. Some projects will not have all 5 milestones. I would like it to figure out if milestone 1 has been completed, then move on to milestone 2, then 3, etc. until it finds the first incomplete milestone. Any thoughts?

[zmbd]

jmaher196:
It might help to have an idea as to how your tables are set up... If Smilie's method isn't working for you then there may be an issue with how the data is being collected and/or the underlying table structure.
-
z

ps:
following Smilies suggestion and pulling something out of thin air; your sql might look something like this using a table named "tbl_timeline" that you tracked the project histories and it might be related to another project table that had the other information:

SELECT tbl_timeline.timeline_id, tbl_timeline.fk_project_id, tbl_timeline.est_startdate, tbl_timeline.est_enddate, tbl_timeline.actl_startdate, tbl_timeline.actl_enddate
FROM tbl_timeline
WHERE (((tbl_timeline.est_enddate)<Date()) AND ((tbl_timeline.actl_enddate) Is Null));

This will return all of the records in tbl_timeline that have no end date entered and the estimated end time is earlier than today's date... you can then group or filter on the other fields as needed...

You might also double check the level of normalization in your database... it is a stumbling block for many people (including myself - usually if I'm having issues with quries it's because of this...) a good basic explanation is at: http://bytes.com/topic/access/insigh...ble-structures

-
z

[jmaher196]

Thank you to both of you. I will work on it today and see if your suggestions work!

[zmbd]

Opps... forgot a field in my fictional query, [Milestone]:

SELECT tbl_timeline.timeline_id, tbl_timeline.fk_project_id, tbl_timeline.milestone, tbl_timeline.est_startdate, tbl_timeline.est_enddate, tbl_timeline.actl_startdate, tbl_timeline.actl_enddate 
FROM tbl_timeline 
WHERE (((tbl_timeline.est_enddate)<Date()) AND ((tbl_timeline.actl_enddate) Is Null)); 
 

Doesn't change the resulting records from the underlying table.