Need exact query to compare two Access tables

1. By compare, do u want to find those matching rows or unmatching rows. Finding unmatching rows in Inventory tells u the additional info, then run this vice versa to find records in picfiles.
MS Access has a simple to use query wizard called Find Unmatched Records - very easy to use, u could try that


to find matching records, Im assuming field names the same in both tables?
in the select, u display what fields u want., if u want all, then do this select *


select inventory.pic
from inventory, picfiles
where inventory.[mfg part number] = picfiles.[mfg part number]
and inventory.pic = picfiles.pic

basically u specifiy tablename.fieldname

the other is

select picfiles.picnumbers
from picfiles, inventory
where picfiles.picnumbers = inventory.picnumbers

Table A: "Inventory"
Table A field to compare with Table B's: "Mfg Part Number"
Table A field to check when the above field matches the one in Table B: "pic"

Table B: "picfiles"
Table B field to compare to above field Table A: "picnumbers"



Not clear, so I have taken the assumptions the fieldnames are the same in both tables
If that is not the case, can u say the exact fields in each table u want to compare against

Or do u want to compare two fields in one table with one field in another?
if so, u can just concenate
WHERE inventory.[mfg part number] & inventory.pic = picfiles.picnumbers

I want to compare the field called "Mfg Part Number" in the table called "Inventory" (Table A) with the field called "picnumbers" in the table called "picfiles" (Table B). If they match, I want a field called "pic" in Inventory (Table A) to be checked. Does this clear it up?

-J

Just made this for another question, it will work for you too I think.

Function CompareTables()
'This function compares the data in one table with another

    Dim db As Database
    Dim rsTable1 As DAO.Recordset
    Dim rsTable2 As DAO.Recordset
    Dim str1 As String
    Dim str2 As String
    Dim str3 As String
    Dim ItemExists As Boolean
   
' JT Open the First table as a recordset
    Set rsTable1 = CurrentDb.OpenRecordset("tbl_Table1", dbOpenTable)
        rsTable1.Index = "PrimaryKey"
' JT Open the Second table as a recordset
    Set rsTable2 = CurrentDb.OpenRecordset("tbl_Table2", dbOpenTable)
        rsTable2.Index = "PrimaryKey"
' JT Check to see if there are records to compare against
    If rsTable1.EOF Then
        GoTo Exit_Out
    End If
' JT Move to the first record in the recordset
    rsTable1.MoveFirst
        Do Until rsTable1.EOF
            str1 = rsTable1![Field1]
' JT Look up the value in the other table
                rsTable2.Seek "=", str1
                ItemExists = IIf(rsTable2.NoMatch, False, True)
                ' If the item exists then set the Yes/No value
                If ItemExists Then
                    rsTable2.fields("CheckBox).value = -1
                End If
Next_Record:
        rsTable1.MoveNext
        Loop
   
    rsTable1.Close
    Set rsTable1 = Nothing
Exit_Out:
    msgbox"No Records to compare"
    rsTable2.Close
    Set rsTable2 = Nothing
End Function

I'll try this, but can I ask you to insert the actual names of my tables and fields in the appropriate places in your code so all I have to do is copy, paste and run it? I'm afraid I will screw it up if i start messing with your code myself.

I appreciate the help so far
Jim

You still need to make sure the index names that you want to use match. You find those in the design view of your tables. Let me know if you need help with that.

Function CompareTables()
'This function compares the data in one table with another

    Dim db As Database
    Dim rsTable1 As DAO.Recordset
    Dim rsTable2 As DAO.Recordset
    Dim str1 As String
    Dim str2 As String
    Dim str3 As String
    Dim ItemExists As Boolean
   
' JT Open the First table as a recordset
    Set rsTable1 = CurrentDb.OpenRecordset("Inventory", dbOpenTable)
        rsTable1.Index = "PrimaryKey"
' JT Open the Second table as a recordset
    Set rsTable2 = CurrentDb.OpenRecordset("picfiles", dbOpenTable)
        rsTable2.Index = "PrimaryKey"
' JT Check to see if there are records in Table1
    If rsTable1.EOF Then
        GoTo Exit_Out
    End If
' JT Check to see if there are records in Table2
    If rsTable2.EOF Then
        GoTo Exit_Out
    End If
' JT Move to the first record in the recordset
    rsTable1.MoveFirst
        Do Until rsTable1.EOF
            str1 = rsTable1.fields("Mfg Part Number").value
' JT Look up the value in the other table
                rsTable2.Seek "=", str1
                ItemExists = IIf(rsTable2.NoMatch, False, True)
                ' If the item exists then set the Yes/No value
                If ItemExists Then
                    rsTable2.fields("pic").value = -1
                End If
Next_Record:
        rsTable1.MoveNext
        Loop
   
    rsTable1.close
    rsTable2.Close
    Set rsTable1 = Nothing
    Set rsTable2 = Nothing
    exit function
Exit_Out:
    msgbox"No Records to compare"
    rsTable1.close
    rsTable2.Close
    Set rsTable1 = Nothing
    Set rsTable2 = Nothing
End Function

J

I want to compare the field called "Mfg Part Number" in the table called "Inventory" (Table A) with the field called "picnumbers" in the table called "picfiles" (Table B). If they match, I want a field called "pic" in Inventory (Table A) to be checked. Does this clear it up?


so u want inventory.pic to be updated for the matching records?
u could try this


update Inventory, picfiles
set pic = True
where inventory.[mfg part number] = picfiles.picnumbers


this find all those records in inventory where its mfg part number matches the picnumbers in picfiles
and sets the pic field to be true


If u run, backup table first

Sometimes, it's the easy answer!

UPDATE tableA, tableB SET tableA.[true] = True WHERE (([tableA].[junk]=[tableB].[junk]));
J

jefftwilley,

I tried your first solution, and I got a Run-time error 3421, Data Type conversion error. When I try to debug, it highlighted this line:

rsTable2.Seek "=", str1

do you see what might be the problem?

Re: your second post with the easy answer - I saw that in another thread already and it doesn't work for me. When I try to substitute my table and field names I get syntax errors all over the place. Remember, I'm not a coder, and I need something I can just copy and paste into Access' VB editor. Thanks for hanging in there with me!

Jim

Jim,
You get the Run-time error 3421, Data Type conversion error. So....
Along with checking indexes if you want to use the SEEK method, we also need to see what the value of str1 is when it tries to seek it. You can do that easily by putting a msgbox right after the statement that defines str1. If it's a null, blank, or anything other than the first record in that field, then there's a problem we'll have to take a closer look at.
J

Thanks Jeff, you got the winner there, worked perfectly!

Jim

P.S., I only needed this as a one-shot deal. My main form now has a checkbox for my client to check when they upload pics. I just had to get them updated. Thx again.

You're most welcome....and Rocki should get credit here too....he's a genious you know!! Hi Rock!!
J

I'm down with that - all I know is a dummy like me had to get things spelled out and yours worked!
I appreciate yours and Rocki's contributions.

-J