Link to home
Start Free TrialLog in
Avatar of John Sheehy
John SheehyFlag 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
Avatar of John Sheehy
John Sheehy
Flag of United States of America image

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
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?
Avatar of aikimark
John

It would be helpful if you posted some representative data.

I assume there are two different tables, but I could be wrong
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

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

Open in new window

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
What is the 512 test?
There are 512 Test that this would cover.
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.
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

Open in new window

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.


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.
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?
Where do the parentheses come in?  Looks like you're dealing with file names.
The parentheses are just addtional tests within that one test.  They litterally are just TST-1 or TST-1(1)
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
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I will test this out and see how it works.  If it does this will help out a lot.
The final solution provded worked like a charm.  I was also able to use that same code to go backwards.