Loop though a (many to many associate) table and update a third table only if all variables = true

[RyanP]

First time really using a many to many relationship and am having a heck of a time figuring out how to accomplish updating all records on Table A only if the values of all records in Table B (associate table) meet a criteria, meanwhile looping for all results of a query based on Table C.

For simplicity:
Table A = Projects
Table B = Many to Many Relationship
Table C = Tasks

If all the tasks associated with a project are finished, "close" the project, otherwise it remains open. Call this function whenever each individual task is closed.

Table B has only its own PKID and an FKID from each of the related tables (the many to many relationship) and a single value (the criteria) from each related table (written programmatically from forms) for purposes of this function.


So...?
Loop through Table B FOR each Table C ID (Select Distinct query based on Table C)
Nested Loop through Table B for every Table A FKID (all records matching the criteria of the first loop)
And if any of the results do not meet the criteria, do not update Table A; otherwise if all results meet the criteria, update Table A.

[June7]

Should not have to store this value in Projects table. This can be calculated when needed.

However, assuming each project has at least one associated task, try:

UPDATE Projects SET Status = "Closed" WHERE NOT ID IN (SELECT ProjectID FROM Tasks WHERE Status="Open")

otherwise, gets more complicated, try:

UPDATE Projects SET Status = "Closed" WHERE ID IN (SELECT ProjectID FROM Tasks GROUP BY ProjectID HAVING Sum(IIf([Status]="Open",1,0))=0)

[RyanP]

Hi' Thanks for the quick response but;

1) Am not storing the value in Table A. That would be impossible as it is a many to many relationship. Was storing it in the associate table (Table B) as that is where the check occurs. (Cannot run the Check on Table C where the initial value is stored because it does not contain any Table A "Projects".

2) The Immediate If only works for checking a single value for True or False...so that won't work because I need to check up to 6 potential values from Table B and update to Table A only if the values for the specific FKID(s) pass the criteria.

3) Each Table A "Project" may have any number of Table C "Tasks" and those tasks may apply to multiple Table A "Projects". Hence the many to many relationship...

4) Note that the provided query is checking the entire table, not only for the specific IDs that are to be checked. That wont work as the check is only to be undertaken for the Table A "Projects" where the unique Table C "Tasks" are being updated. (The source of the specific IDs to check is a query based on Table B)

Despite not being able to use it as is due to point number 2/4, I did run it as you had wrote it (with my own object names of course) and it updates only records where the field being checked with the immediate IF are NULL?

UPDATE TABLEA SET Disposition = VAR
WHERE ((([TABLEAID]) In (SELECT TABLEBFKID FROM TABLEB GROUP BY TABLEB HAVING Sum(IIf([TABLEBVALUE]=VAR,1,0))=0)));

Should not have to store this value in Projects table. This can be calculated when needed.

However, assuming each project has at least one associated task, try:

UPDATE Projects SET Status = "Closed" WHERE NOT ID IN (SELECT ProjectID FROM Tasks WHERE Status="Open")

otherwise, gets more complicated, try:

UPDATE Projects SET Status = "Closed" WHERE ID IN (SELECT ProjectID FROM Tasks GROUP BY ProjectID HAVING Sum(IIf([Status]="Open",1,0))=0)

[rpeare]

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:

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.

[RyanP]

Awesome... your tables example is essentially what I have and the query works functionally* however

1) the Immediate If only checks for one value 3 of the 6 potential values in this field would provide a valid result to proceed with the UPDATE query and need to be checked in tandem... I attempted to duplicate the line after "HAVING"" for each variable but got a "Cannot have aggregate function in WHERE clause"... I'm doing something wrong there obviously :0

2) This checks the whole table in lieu of only the specific "Project" IDs associated with the Task. (this is why I thought I needed to put this all in a loop based on a recordset..)

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:

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.

[rpeare]

I don't understand, are you saying in your version of the tblProjectTask table you have columns that are Task1, Task2, Task3 (a non-normalized structure)? Or are you saying the 'status' can be one of 3 things and still be considered 'complete'?

[RyanP]

Sorry, I replied too early...

For my purposes, updating the project(s) dispositions when the task's disposition is updated. Not setting it explicitly to "Complete" as you had in your example.
So the latter; saying the 'task disposition' can be one of 3 things and still be considered 'new disposition'

I did resolve the aggregate function issue I created by putting in the OR clause into your example as follows: HAVING (((Count([projtask_id])=Sum(IIf([Status_Desc]= 'pending evaluation' OR 'pending approval' OR 'closed',1,0)))=True))));
I had initially thought to duplicate the whole (((Count([projtask_id])=Sum(IIf([Status_Desc]= 'closed',1,0)))=True)))); which in retrospect was quite silly... my bad.

[rpeare]

that statement will not work you would have to use

sum(iif([Status_desc] = 'pending evaluation' OR [Status_Desc] = 'pending approval' or [Status_desc] = 'closed', 1, 0))

you can't use AND or OR without stating the field it applies to.

[RyanP]

Yes, that is correct. thanks... copy pasted the wrong text

[RyanP]

that statement will not work you would have to use

sum(iif([Status_desc] = 'pending evaluation' OR [Status_Desc] = 'pending approval' or [Status_desc] = 'closed', 1, 0))

you can't use AND or OR without stating the field it applies to.

As noted the example you provided worked for an update query but I have so far been unable to mod for an Insert query. (Error: Query input must contain at least one table or query)

INSERT INTO TableA_Updates (UpdateDESC,UpdateTime,UpdatePerson, UpdateV)
VALUES ('text' & ' ' & form_field_reference & ' ' & 'text' & ' ' & form_field_reference &'.' & 'text' & ' ' & form_field_reference & ' ' &'text', form_field_reference, form_field_reference, form_field_reference)
WHERE (((TableA_Updates.TableAFKID) In (SELECT TableB.TableAFKID
FROM TableB
GROUP BY TableB.TableAFKID
HAVING (((Count([TableB].[TableBPKID])=Sum(IIf(TableB.TableCFKID = form.TableCFKID,1,0)))=True))));

The first part works on its own however need it to update TableA_Updates only if there is a record on TableB with both TableA & Table C FKIDs...
The latter half works fine with an Update query (different usage) however cannot get it to work on an insert query.