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]
The table [Implemented]![Implementat
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
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]
The table [Implemented]![Implementat
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
Last Comment
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?
So how do I tell the count query to store the CountOfComplainceStatus into a variable?
John
It would be helpful if you posted some representative data.
I assume there are two different tables, but I could be wrong
It would be helpful if you posted some representative data.
I assume there are two different tables, but I could be wrong
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
ASKER
Is there a better way of writing that?
Could you prepare sample DB and upload it? Please, show also expected result
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
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
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
What is the 512 test?
ASKER
There are 512 Test that this would cover.
So TST-1 through TST-512
But if not it's not a big deal.
So TST-1 through TST-512
But if not it's not a big deal.
You just put the code inside a loop. Each time through the loop, you construct the TST- value with concatenation.
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.
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
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.
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.
So when an individual test is done. It does the check, and updates across the board.
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?
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?
Where do the parentheses come in? Looks like you're dealing with file names.
ASKER
The parentheses are just addtional tests within that one test. They litterally are just TST-1 or TST-1(1)
ASKER
If a loop can't be done then it's not a big deal. Just was seeing if we could.
Thanks
John
Thanks
John
ASKER CERTIFIED SOLUTION
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.
ASKER
I will test this out and see how it works. If it does this will help out a lot.
ASKER
The final solution provded worked like a charm. I was also able to use that same code to go backwards.
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