VBA to update values in one table based on another table

[KP_SoCal]

Calling any VBA genius! Here’s what I’m trying to do. I attached an excel document that ties in with my question.

In Query1, I pull my raw data to create Table1. I need Query2 to evaluate the fields in each row of Table1 that match the Master Table and clear the fields (or set to Null) that do not match. The only exception would be, if NONE of the fields in Table1 match fields in the Master Table, then set the Field Qty value to NULL in Table1. Finally, I create a simple query that groups the results of the Table1 update to give me subtotals for each row. In order for this to make better sense, please see my attached excel document.

The part that I’m stuck on is Query2. I know VBA holds the answer in making this process more efficient as opposed to have to write a new query for each and every possible combination. Whoever figures this out wins the Academy Award of Super Genius in my book.

Thanks so much for any ideas!

 

[vbaInet]

A combination of DLookups, IF... ELSEIF... ELSE statements and an Update/Append query would solve this. Have you been using those?

Whoever figures this out wins the Academy Award of Super Genius in my book.

Are we guaranteed you will win a Nobel prize?

 

[KP_SoCal]

I attached a database that further demonstrates the end results that I'm looking to achieve without having to relay on multiple append queries each with their own different criteria.

The data I'm actually working with could include signicantly more combinations that would require endless append queries. The room for error would increase substantially.

I'm barely a novice in VBA, but I'm sure there is a module that would dynamically accomplish what I need. Does that make sense?

 

[vbaInet]

Good to know you're not a novice to VBA. Here's the idea:

Two recordsets, four loops (one inside the other), a variable to hold a count of how many fields matched, IF... ELSE statement.

- One recordset for each table
- First outter loop for each record in Table1, second down for each field in Table, third down for each record in Master table, and fourth for each field in Master.
- The variable will count how many fields were matched
- The IF ELSE statement will evaluate if a field matches for each record
- Another IF statement (without the ELSE) will evaluate whether the variable is 0 and set the Qty field to 0.

Here's a thread that will give you some idea on how to approach this:

http://www.access-programmers.co.uk/forums/showthread.php?t=187647&page=4

Happy coding!

 

[KP_SoCal]

Thanks, I'll give this a whirl. One correction I need to make is that what I meant by barely being a VBA novice is that I'm not even close to achieve a novice level in VBA. Basically, I'm a VBA ignoramous pretty much. Ha!

But thanks for your suggestion. I'll try to make sense of it and see if I can get it to work. What you're describing to me sounds exactly like what I need. You may be getting the Acadamy Award afterall.

Hopefully I can figure out how to impliment. Thanks again for your suggestion.

 

[vbaInet]

One correction I need to make is that what I meant by barely being a VBA novice is that I'm not even close to achieve a novice level in VBA.

I read that wrongly I must need sleep!! haha!

But thanks for your suggestion. I'll try to make sense of it and see if I can get it to work. What you're describing to me sounds exactly like what I need. You may be getting the Acadamy Award afterall.

I'm glad you're actually going to take a plunge at the deep end. It shouldn't be too difficult to figure, but if you get stock on logic or syntax, just let me know. I shall put the Award on my virtual mantlepiece if I win. Come on vbaInet.

Hopefully I can figure out how to impliment. Thanks again for your suggestion.

You're welcome.

 

[KP_SoCal]

vbaInet, help!!! I'm having a VBA ignoramous attack!!! (laugh/joke)

I took a stab at implimenting the code based on one of your older threads (see below), but I'm just not sure where to go from here.

Without question your response below describes the solution of exactly of I'm trying to achieve. I'm just really stuck on the implimentation. Would you be able to provide any other example? Really, really, really appreciate it. Thanks!

Two recordsets, four loops (one inside the other), a variable to hold a count of how many fields matched, IF... ELSE statement.

- One recordset for each table
- First outter loop for each record in Table1, second down for each field in Table, third down for each record in Master table, and fourth for each field in Master.
- The variable will count how many fields were matched
- The IF ELSE statement will evaluate if a field matches for each record
- Another IF statement (without the ELSE) will evaluate whether the variable is 0 and set the Qty field to 0.

Option Compare Database
Option Explicit
Public Sub TestingCode()
 
Dim rstTable1 As Recordset, rstMaster As Recordset
    Dim oldField1 As String, oldField2 As String, oldField3 As String, oldField4 As String, oldField5 As String, oldField6 As String
    Dim tempCounter As Long, yRow As Long
Set rstTable1 = CurrentDb.OpenRecordset("Table1").Clone
tempCounter = 1
Set rstMaster = CurrentDb.OpenRecordset("tblMaster").Clone
tempCounter = 1
 
With rstTable1
    If .BOF = False Then
            If MsgBox("Backup your table before continuing" & vbCrLf & "Continue?", vbYesNo) = vbYes Then
                ' Fill the recordset
                .MoveLast
                .MoveFirst
                For yRow = 0 To .RecordCount - 1
                    If CStr(!Field1) = oldField1 Then
                        If CStr(!Field2) = oldField2 Then
                            If CStr(!Field3) = oldField3 Then
                                If CStr(!Field4) = oldField4 Then
                                    If CStr(!Field5) = oldField5 Then
                                        If CStr(!Field6) = oldField6 Then
                                            tempCounter = 1
                                        End If
                                    Else
                                        tempCounter = 1
                                    End If
                                Else
                                    tempCounter = 1
                                End If
                            Else
                                tempCounter = 1
                            End If
                        Else
                            tempCounter = 1
                        End If
                    Else
                        tempCounter = 1
                    End If
 
                    ' Update the counter field
                    .Edit
                    !FieldCtr = tempCounter
                    .Update
                    ' Save the values for checking against the next record
                    oldField1 = CStr(!Field1)
                    oldField2 = CStr(!Field2)
                    oldField3 = CStr(!Field3)
                    oldField4 = CStr(!Field4)
                    oldField5 = CStr(!Field5)
                    oldField6 = CStr(!Field6)
 
                    ' Increment tempCounter
                    tempCounter = tempCounter + 1
                    .MoveNext
                Next
                MsgBox "Finished updating."
            End If
        Else
            MsgBox "There are no records"
        End If
    End With
    rstTable1.Close
    Set rstTable1 = Nothing
End Sub

 

[vbaInet]

That thread was just for you to use as a guideline as to how you could implement recordsets. There are some things you don't need.

I'll see what I can do.

 

[KP_SoCal]

You rock!!! Please get some sleep tough. I can always touch bases with you tomorrow. Thanks again. Cheers!!!

 

[vbaInet]

I am doing so now

Here's most of the code. Amend correctly and do the inner loop for each field in Master. It would be good if all the fields are positioned at athe same place between tables.

    Dim db As DAO.Database
    Dim rstTable1 As DAO.Recordset, rstMaster As DAO.Recordset, rstField1 As DAO.Recordset
    Dim yRow1 As Long, yRow2 As Long, yRow3 As Long, xCol1 As Long, xCol2 As Long
    Dim whereString As String, counter As Integer
    
    Set db = CurrentDb
    ' First recordset to get the distinct field1 values used to set the WHERE clause later in the code
    Set rstField1 = db.OpenRecordset("SELECT DISTINCT [Field1] FROM Table1 ORDER BY [Field1]").Clone
    
    ' The recordsets for table1 and Master
    Set rstTable1 = db.OpenRecordset("SELECT * FROM Table1 GROUP BY [Field1] ORDER BY [Field1]").Clone
    Set rstMaster = db.OpenRecordset("SELECT * FROM tblMaster GROUP BY [Field1] ORDER BY [Field1]", dbOpenSnapshot).Clone
    
    counter = 1
    
    If rstField1.BOF = False Then
        MsgBox "No values in Field1"
    ElseIf rstTable1.BOF = False And rstMaster.BOF = False Then
        If MsgBox("Backup your tables before continuing" & vbCrLf & "Continue?", vbYesNo) = vbYes Then
            ' Fill the recordsets
            rstField1.MoveLast
            rstField1.MoveFirst
            rstTable1.MoveLast
            rstTable1.MoveFirst
            rstMaster.MoveLast
            rstMaster.MoveFirst
            
            For yRow1 = 0 To rstField1.RecordCount - 1
                ' Filter by the group declared before
                rstTable1.Filter = "[Field1] = '" & rstField1.Fields(0).value & "'"
                rstTable1.Filter = "[Field1] = '" & rstField1.Fields(0).value & "'"
                
                For yRow2 = 0 To rstTable1.RecordCount - 1
                    For xCol1 = 0 To rst1.Fields.count - 1
                        For yRow3 = 0 To rstMaster.RecordCount - 1
                            If rstTable1.Fields(xCol) <> rstMaster.Fields(xCol) Then
                                ' Update the field
                                .Edit
                                rstTable1.Fields(xCol) = ""
                                .Update
                                
                                ' Increment the counter
                                counter = counter + 1
                            End If
                            rstMaster.MoveNext
                        Next
                        If counter = 5 Then
                            ' Update the quantity
                            .Edit
                            !FieldQty = 0
                            .Update
                            
                            ' Reset the counter
                            counter = 1
                        End If
                    Next
                    rstTable1.MoveNext
                Next
                rstField1.MoveNext
            Next
            MsgBox "Finished updating."
        End If
    Else
        MsgBox "There are no records in either or both tables"
    End If
    rstField1.Close
    rstTable1.Close
    rstMaster.Close
    Set rstField1 = Nothing
    Set rstTable1 = Nothing
    Set rstMaster = Nothing

Code hasn't been run or tested.

 

[KP_SoCal]

vbaInet...wow, thanks for piecing together all that code, but I'm afraid I must respite. I'm just not strong enough in VBA to troubleshoot the errors I'm getting or impliment my own loops (I have some homework to do).

Even though I was not able to impliment (due to my own lack of knowledge in VBA), it appears you described to a Tee what needs to happen for this to work.

Here's the idea:

Two recordsets, four loops (one inside the other), a variable to hold a count of how many fields matched, IF... ELSE statement.

- One recordset for each table
- First outter loop for each record in Table1, second down for each field in Table, third down for each record in Master table, and fourth for each field in Master.
- The variable will count how many fields were matched
- The IF ELSE statement will evaluate if a field matches for each record
- Another IF statement (without the ELSE) will evaluate whether the variable is 0 and set the Qty field to 0.

Happy coding!

So you are the VBA Academy Award winner in my book. Please see attached image for your virtual trophy case. Take care!

KP

 

[vbaInet]

You may need to do some homework to be up to scratch with this stuff KP SoCal

What I explained in that post was actually based on what you said, but when I looked at your spreadsheet the implementation changed. When you said this:

In Query1, I pull my raw data to create Table1. I need Query2 to evaluate the fields in each row of Table1 that match the Master Table and clear the fields (or set to Null) that do not match.

That implied the two tables had the same number of fields, the same number of records and appear in the same sequence, which led me to that suggestion.

The code is almost there but needs refining.

Create another spreadsheet (just like before) and put on there the exact names of the fields on both tables and their exact order in which they appear in your tables.

Thanks for the Award. Can I touch it?

 

[vbaInet]

There are other ways to achieve this using multiple queries which have subqueries, one for each field and then having another query with a subquery that checks each field.

I have gone ahead with refining the code anyway. If the names of the fields were the exactly the same on each table or were in the same order then the code can be further optimised. I've not tested it but it compiles so here it is:

    Dim db As DAO.Database
    Dim rstTable1 As DAO.Recordset, rstField1 As DAO.Recordset
    Dim yRow1 As Long, yRow2 As Long, counter As Integer
    
    Set db = CurrentDb
    ' Recordset to get the distinct Field1 values used to set filter tblTable1's recordset later on
    Set rstField1 = db.OpenRecordset("SELECT DISTINCT [Field1] FROM Table1 ORDER BY [Field1]").Clone
    
    ' The recordset for Table1
    Set rstTable1 = db.OpenRecordset("SELECT * FROM Table1 GROUP BY [Field1] ORDER BY [Field1]").Clone
    
    counter = 0
    
    If rstField1.BOF = False Then
        MsgBox "No values in Field1"
    ElseIf rstTable1.BOF = False And rstMaster.BOF = False Then
        If MsgBox("Backup your tables before continuing" & vbCrLf & "Continue?", vbYesNo) = vbYes Then
            ' Fill the recordsets. To do this, move to the last record. Move back to first to start your search.
            rstField1.MoveLast
            rstField1.MoveFirst
            rstTable1.MoveLast
            rstTable1.MoveFirst
            
            For yRow1 = 0 To rstField1.RecordCount - 1
                With rstTable1
                    ' Filter the records of Table1's recordset by the value of rstField1's current record
                    .Filter = "[Field1] = '" & rstField1.Fields(0).value & "'"
                    
                    For yRow2 = 0 To .RecordCount - 1
                        ' Field2
                        If DCount("[Field2]", "tblMaster", "[Field1] = '" & ![Field1] & "' " & _
                            "AND [Field2] = '" & ![Field2] & "'") = 0 Then
                            ' Update that field to empty string
                            .Edit
                            ![Field2] = Null
                            .Update
                            
                            counter = 1
                        End If
                        
                        ' Field3
                        If DCount("[Field3]", "tblMaster", "[Field1] = '" & ![Field1] & "' " & _
                            "AND [Field3] = " & ![Field3]) = 0 Then
                            ' Update that field to empty string
                            .Edit
                            ![Field3] = Null
                            .Update
                            
                            ' Increment counter
                            counter = counter + 1
                        End If
                        
                        ' Field4
                        If DCount("[Field4]", "tblMaster", "[Field1] = '" & ![Field1] & "' " & _
                            "AND [Field4] = " & ![Field4]) = 0 Then
                            ' Update that field to empty string
                            .Edit
                            ![Field4] = Null
                            .Update

                            counter = counter + 1
                        End If
                        
                        ' Field5
                        If DCount("[Field5]", "tblMaster", "[Field1] = '" & ![Field1] & "' " & _
                            "AND [Field5] = " & ![Field5]) = 0 Then
                            ' Update that field to empty string
                            .Edit
                            ![Field5] = Null
                            .Update

                            counter = counter + 1
                        End If
                        
                        ' Field6
                        If DCount("[Field6]", "tblMaster", "[Field1] = '" & ![Field1] & "' " & _
                            "AND [Field6] = " & ![Field6]) = 0 Then
                            ' Update that field to empty string
                            .Edit
                            ![Field6] = Null
                            .Update

                            counter = counter + 1
                        End If
                            
                        ' Counter will be 5 if none of the fields have related values in tblMaster
                        If counter = 5 Then
                            ' Update the quantity because there was no match
                            .Edit
                            !FieldQty = 0
                            .Update
                        End If
                        
                        ' Reset the counter
                        counter = 0
                        
                        ' Move to next record of Table1
                        .MoveNext
                    Next
                End With
                
                ' Move to next record of Field1
                rstField1.MoveNext
            Next
            MsgBox "Finished updating."
        End If
    Else
        MsgBox "There are no records in either or both tables"
    End If
    rstField1.Close
    rstTable1.Close
    Set rstField1 = Nothing
    Set rstTable1 = Nothing

Go through each line of code amending the field names or table names where necessary, not the names of the recordset objects.

If you have a huge database, then you should consider using the queries as suggested above. Here's a link:
http://allenbrowne.com/subquery-01.html

 

[KP_SoCal]

Great! Thanks for the bonus effort in getting this helpful information for me. No wonder you were nominated for the VBA Academy Award this year!

 

[vbaInet]

Just a nominee, not even a winner? hehe!!

You're welcome. Did it work for you?

 

[KP_SoCal]

Of course you won... See what I get for speed typing, ha!

I haven't had a chance to test it yet, but it looks fairly intuitive. If I run into any snags, I post another thread.

Have an outstanding week and thanks again for everything!

 

[vbaInet]

Goodie

Like I mentioned earlier, if the field names on both tables were the same or their order in which they appear were the same, then that code could be greatly shortened and optimised.

Have a fab week too.