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?

Get vaccinated; Social distance; Wear a mask

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2014

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
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