Compare two fields and update a third field if appropriate

[dknj30]

I'm new to VBA, intermediate SQL. I've been scouring the web for an answer - and can't find anything similar... or perhaps I'm searching for the wrong thing! I'm using Access 2007.

I want to compare two columns from two different tables and then update another column.
Table A is an audit trail.
ID--CHANGETYPE--OBJECTID---BEFORE--AFTER--VALIDATED?
1---ColorChange--Object1-----Red------Blue----No

Table B is the current status of the object:
OBJECTID---COLOR
Object1-----Blue

So using the sample data above: Table A says Object1 should be Blue now because it had a color change. Table B says Object1 is currently Blue.

So I would like to create some vb code that goes something like this:
For each row...
If tableA.ChangeType = "ColorChange" then
if tableA.After = tableB.Color then
set tableA.Validated to "Yes"

Thanks in advance for the assistance!!!

 

[Galaxiom]

Make an INNER JOIN ObjectID and set Validated as true
WHERE A.After = B.Color

However, note that storing both B.Color and A.Validated is a normalization error.

 

[dknj30]

@GalaxiomAtHome - That doesn't help with setting the validation field to "yes", nor does it take into account that the rule only applies if the changetype is "ColorChange".

 

[Galaxiom]

UPDATE A INNER JOIN B ON A.ObjectID = B.ObjectID
SET A.Validation = True
WHERE A.After = B.Color
AND A.ChangeType = "ColorChange";

 

[dknj30]

Thanks! I got the vb code...

Set results = CurrentDb.OpenRecordset(JoinedBothTables)
If results.RecordCount <> 0 Then
With results
.MoveFirst
Do While Not .EOF
If ![ChangeType] = "ColorChange" Then
If !After= !Color Then
CurrentDb.Execute ("update tableA set validated = 'yes where id=" & ![ID])
End If
End If
.MoveNext
Loop
End With
End If

 

[Galaxiom]

Thanks! I got the vb code...

I guess it is a solution but why use all the VB code and a recordset when a query can do the whole job in a tiny fraction of the time? Queries are the fastest way to change data even compared to efficient use of recordsets.

You are running individual Update queries for each instance of the match.

I also note you are using a string to record the Validated status. A Boolean is far more efficient at holding Yes/No. Even if you were recording more than just Yes/No it would be better done using an integer to represent the value with a Format string (can manage up to four values including Null) or a Lookup (as many values as you care to use) to display the word.

Likewise the ChangeType could be held more efficiently as an integer.

Also note in your VBA code, the two separate If tests could be combined.

If ![ChangeType] = "ColorChange" And !After= !Color Then
CurrentDb.Execute ("update tableA set validated = 'yes where id=" & ![ID])
End If