troubleshooting Question

Using an Update Query (Not So Simple)

Avatar of John Sheehy
John SheehyFlag for United States of America asked on
Microsoft AccessVBASQL
22 Comments1 Solution74 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 22 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros