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 [1] and [45] 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.
Make sense?
John
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.