These are my tables:
tblProject
Proj_ID |
Proj_Name |
Status_ID |
1 |
Project A |
|
2 |
Project B |
|
3 |
Project C |
|
tblTask
Task_ID |
Task_Name |
1 |
Task A |
2 |
Task B |
3 |
Task C |
4 |
Task D |
5 |
Task E |
tblStatus
Status_ID |
Status_Desc |
1 |
Hold |
2 |
Incomplete |
3 |
Complete |
tblProjectTask
ProjTask_ID |
Proj_ID |
Task_ID |
Status_ID |
1 |
1 |
1 |
1 |
2 |
1 |
2 |
2 |
3 |
1 |
3 |
1 |
4 |
1 |
4 |
3 |
5 |
2 |
5 |
3 |
6 |
2 |
4 |
3 |
7 |
2 |
3 |
3 |
8 |
3 |
1 |
1 |
9 |
3 |
2 |
3 |
This is my query:
Code:
UPDATE tblProject SET tblProject.Status_ID = DLookUp("[Status_ID]","tblStatus","[Status_Desc] = 'Complete'")
WHERE (((tblProject.[proj_id]) In (SELECT tblProjectTask.Proj_ID
FROM tblProjectTask LEFT JOIN tblStatus ON tblProjectTask.Status_ID = tblStatus.Status_ID
GROUP BY tblProjectTask.Proj_ID
HAVING (((Count([projtask_id])=Sum(IIf([Status_Desc]='complete',1,0)))=True));)));
This assumes once a project is complete it will never become 'incomplete' again (i.e. a new step is added or data entry was screwed up), you'll need a separate process to 'uncomplete' a project or modify this query to do it.