Link to home
Start Free TrialLog in
Avatar of Lend2901
Lend2901Flag for United States of America

asked on

Comparing 2 tables in Microsoft Access with true or false output.

Hi Experts,
I was recently delegated for this and really rusty. I need codes or advise on how to compare 2 tables in Access database where if they dont match.or match, 1) will produce false /or true result, 2) will add another column and place the results there. The database was merged and need investigate the false output.

Ex: Field1                                Field2                                        <newField>
2-B00-BA0-22913-C-A-000      2-B00-BA0-22913-C-A-000        true
2-B00-BA0-22913-C-A-000      2-B00-BA0-32048-E-A-000        false

I currently have Access 2000.  Codes will be much appreciated, and any advise will be great. Excel is the easiest way to take care of this but was hoping someone can help me with Access.
     
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
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
Avatar of Lend2901

ASKER

forgive me for being a newby, but when I try that, gave me compile error: Expected:Case

I substituted the field1,field2 with my own fieldname
Avatar of compTrack
compTrack

Hey There,

What you need to do is create a table in MS Access with three fields. For sake of argument, lets call the first column "Field1", the second column "Field2" and the third column "Results." Let's call the table tblCompare. Put in all the values for Field1 and put in all the values for Field2.

Now, in an Access Form, maybe you can create a button and on the click event of the button you can put in the following code:

Dim sqlString As String
Dim rstCompare As DAO.Recordset

sqlString = "SELECT * FROM tblCompare"
Set rstCompare = CurrentDb.OpenRecordset(sqlString, dbOpenDynamic)
rstCompare.MoveFirst

While Not rstCompare.EOF
    If StrComp(rstCompare!Field1, rstCompare!Field2, vbTextCompare) Then
        With rstCompare
            .Edit
            !Result = True
            .Update
        End With
    Else
        With rstCompare
            .Edit
            !Result = False
            .Update
        End With
    End If
    rstCompare.MoveNext
Wend

rstCompare.Close
Set rstCompare = Nothing


Hope this helps.

Keep us posted.

Best Regards,

compTrack
Lend2901,

first thing that should know.
when posting question, don't use aliases for the names of the objects in your database.

<forgive me for being a newby, but when I try that, gave me compile error: Expected:Case>

where did you place the sql statement..
 this

select field1,field2, iif([field1]=[field2],"True","False") as NewField
from tableX

is a query..

do you know how to create a query?
thank you for the response ..I will test it tomorrow and will get back on updates :-)
I did Access basic programming from 6 years back and wanted to go back to coding.  The database is big and got most of what I need except this one where Im struggling. The code you pasted ran without any error this time, but the results (true or false) is not pasting on the <results> cell. Im trying to look into seeing what else Im missing.
Lend2901,

it will be a lot easier to see the mistake if you will present the query that you did.
You wrote, you need to compare two tables. Thus you need a query like this:

SELECT O.[fieldToCompare], 'both' AS [Source] FROM [tableA] O
WHERE EXISTS (SELECT 1 FROM [tableB] I WHERE I.[fieldToCompare] = O.[fieldToCompare])

UNION ALL

SELECT O.[fieldToCompare], 'only tableA' AS [Source] FROM [tableA] O
WHERE NOT EXISTS (SELECT 1 FROM [tableB] I WHERE I.[fieldToCompare] = O.[fieldToCompare])

UNION ALL

SELECT O.[fieldToCompare], 'only tableB' AS [Source] FROM [tableB] O 
WHERE NOT EXISTS (SELECT 1 FROM [tableA] I WHERE I.[fieldToCompare] = O.[fieldToCompare])

Open in new window


mfG
--> stefan <--
Apology for the delay, but tried this solution and tweak it more to get the output I am seeking for.  Bottomline, it worked. :-) Thank you