Avatar of John Sheehy
John Sheehy
Flag for United States of America asked on

Using an Update Query (Not So Simple)

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
Microsoft AccessVBASQL

Avatar of undefined
Last Comment
John Sheehy

8/22/2022 - Mon
John Sheehy

ASKER
OK, so here is an update.  I wrote a query that does the count.  So for one test it shows me there are 9 Compliant and 2 Non-Compliant,.
How do I get the query to save both those results?  Some thing like this:

Dim strCom as string
Dim strNonCom as string

strCom = ???????
strNonCom = ???????

I know how to transfer the SQL into VBA.


Thanks
John
John Sheehy

ASKER
One more update.  Changed the count query to tell me only the non-compliant

So how do I tell the count query to store the CountOfComplainceStatus into a variable?
aikimark

John

It would be helpful if you posted some representative data.

I assume there are two different tables, but I could be wrong
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
John Sheehy

ASKER
So this what I came up with:

Dim strSQL As String
Dim db As Database
Dim rst As Recordset
Dim strCount As Integer

Set dbs = CurrentDb

strCount = DCount("TRID", "Table - Test Results (Dynamic)", "[Compliance Status1] = 'Non-Compliant' and AIS = Eval ('[Forms]![Form - Main]![HidAIS]') and [Control Acronym]='TST-1'")
If strCount = 0 Then
    strSQL = "UPDATE [Table - Test Results (Dynamic)]" _
    & "INNER JOIN [Table - Implementation Plan] ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number]" _
    & "SET [Table - Implementation Plan].[Implementation Status] = 'Implemented' " _
    & "WHERE ((([Table - Test Results (Dynamic)].[Control Acronym]) Like 'TST-1')" _
    & "AND (([Table - Test Results (Dynamic)].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')) AND (([Table - Implementation Plan].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')));"
    CurrentDb.Execute strSQL
Else:
    strSQL = "UPDATE [Table - Test Results (Dynamic)]" _
    & "INNER JOIN [Table - Implementation Plan] ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number]" _
    & "SET [Table - Implementation Plan].[Implementation Status] = 'Planned' " _
    & "WHERE ((([Table - Test Results (Dynamic)].[Control Acronym]) Like 'TST-1')" _
    & "AND (([Table - Test Results (Dynamic)].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')) AND (([Table - Implementation Plan].AIS)=Eval('[Forms]![Form - Main]![HidAIS]')));"
    CurrentDb.Execute strSQL
End If

Open in new window

John Sheehy

ASKER
Is there a better way of writing that?
als315

Could you prepare sample DB and upload it? Please, show also expected result

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
aikimark

In the following version, I've assumed that the AIS field is a string field.

Dim strSQL As String
Dim db As Database
Dim rst As Recordset
Dim strCount As Integer
Dim strNewValue As String      'new variable
Dim strSelectedAIS As String   'new variable

Set dbs = CurrentDb

strSelectedAIS = Eval("[Forms]![Form - Main]![HidAIS]")

strCount = DCount("TRID", _
				  "Table - Test Results (Dynamic)", _
				  "[Compliance Status1] = 'Non-Compliant' " _
				    & "and AIS = '" & strSelectedAIS & "' " _
					& "and [Control Acronym]='TST-1'")
If strCount = 0 Then
    strNewValue = "Implemented"
Else
    strNewValue = "Planned"
End If


strSQL = "UPDATE [Table - Test Results (Dynamic)] " _
	& "INNER JOIN [Table - Implementation Plan] " _
	& "ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number] " _
	& "SET [Table - Implementation Plan].[Implementation Status] = '" & strNewValue & "' " _
	& "WHERE (([Table - Test Results (Dynamic)].[Control Acronym]) = 'TST-1') " _
	& "AND (([Table - Test Results (Dynamic)].AIS)='" & strSelectedAIS & "') " _
	& "AND (([Table - Implementation Plan].AIS)='" & strSelectedAIS & "') ;"

CurrentDb.Execute strSQL

Open in new window

John Sheehy

ASKER
aikimark, Like what ya did there.  If there any way I can have this loop through the 512 test?  It would help eliminate having to write this 512 times..

I wish I could upload the DB that this was in, but it's quite large and trying to water it down would be against policy.  

John
aikimark

What is the 512 test?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
John Sheehy

ASKER
There are 512 Test that this would cover.
So TST-1 through TST-512

But if not it's not a big deal.
aikimark

You just put the code inside a loop.  Each time through the loop, you construct the TST- value with concatenation.
John Sheehy

ASKER
See this is the one area I am lacking in.  I don't normally do loops so I have no idea how to do that.  Would you happen to be able to provide an example.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
aikimark

Dim strSQL As String
Dim db As Database
Dim rst As Recordset
Dim strCount As Integer
Dim strNewValue As String      'new variable
Dim strSelectedAIS As String   'new variable
Dim strTST As String           'new variable
Dim lngLoop As Long            'new variable

Set dbs = CurrentDb

strSelectedAIS = Eval("[Forms]![Form - Main]![HidAIS]")

For lngLoop = 1 to 512
    strTST = "TST-" & lngLoop
    strCount = DCount("TRID", _
                  "Table - Test Results (Dynamic)", _
                  "[Compliance Status1] = 'Non-Compliant' " _
                    & "and AIS = '" & strSelectedAIS & "' " _
                    & "and [Control Acronym]='" & strTST & "'")
    If strCount = 0 Then
        strNewValue = "Implemented"
    Else
        strNewValue = "Planned"
    End If


    strSQL = "UPDATE [Table - Test Results (Dynamic)] " _
        & "INNER JOIN [Table - Implementation Plan] " _
        & "ON [Table - Test Results (Dynamic)].[Control Acronym] = [Table - Implementation Plan].[Control Number] " _
        & "SET [Table - Implementation Plan].[Implementation Status] = '" & strNewValue & "' " _
        & "WHERE (([Table - Test Results (Dynamic)].[Control Acronym]) = '" & strTST & "') " _
        & "AND (([Table - Test Results (Dynamic)].AIS)='" & strSelectedAIS & "') " _
        & "AND (([Table - Implementation Plan].AIS)='" & strSelectedAIS & "') ;"

    CurrentDb.Execute strSQL

Next

Open in new window

PatHartman

The problem begins with storing calculated data.  I won't go into why calculated data should not be stored, there are thousands of articles available that talk about data normalization.


I didn't go through all the posts in depth so I apologize if I missed something relevant but updating the entire table with the calculated values solves the problem only for the time being.  If you don't update the table every, single time, anyone changes anything relevant, you will immediately be back to where you started.  Deep do do with bad data.


John Sheehy

ASKER
Pat, that bring up a great point and we thought about that today.
So when an individual test is done. It does the check, and updates across the board.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
John Sheehy

ASKER
aikimark,

In regards to your response.  The Loop looks pretty straight forward.  How would I handle different TST Names?
Like TST-1, TST-1(1), TST-2, TST-2(1), TST-2(2)...etc

Would an array work?  Or a case?
aikimark

Where do the parentheses come in?  Looks like you're dealing with file names.
John Sheehy

ASKER
The parentheses are just addtional tests within that one test.  They litterally are just TST-1 or TST-1(1)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Sheehy

ASKER
If a loop can't be done then it's not a big deal.  Just was seeing if we could.

Thanks
John
ASKER CERTIFIED SOLUTION
aikimark

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Sheehy

ASKER
I will test this out and see how it works.  If it does this will help out a lot.
John Sheehy

ASKER
The final solution provded worked like a charm.  I was also able to use that same code to go backwards.
Your help has saved me hundreds of hours of internet surfing.
fblack61