I know how to use update queries. Normally when I use them they are simple.
If this value = this value then update this field to this value.
That normally works fine for me.
However, I have ran into a slight issue.
The table [TestResults]![Compliance] allows two values. Compliant or Non-Compliant
The table [Implemented]![Implementation] also allows two values. Implemented or Planned
So if this was a one for one. Meaning One test for one Implementation then this would be a simple Update Query. But it is not. I could have 5 Tests for one implementation and 10 for another.
So if any of those test fail then the Implementation is set to Planned.
I have it partially done.
If any one test is Non-Compliant then the Implementation is set to Planned.
But if all the tests are Compliant I can not get it to set the Implementation to Implemented.
How do I tell the Update Query to check and see if all the tests are complaint for a particular test and if they are set the Implementation to Implemented.
I tried the range. Between  and  But that only shows me the ones that are Compliant.
So for example. Test 2 has 11 tests in it. If I have 6 of them that are Non-Compliant then it will only show me the 5 that are.
I need it to check and see if that record set has any Non-Complaint and if so, move onto the next record set and it that one has 0 Non-Compliant then set the Implementation to Implemented.