[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 427
  • Last Modified:

Need exact query to compare two Access tables

Hi,

I need to compare two tables containing alphanumeric part numbers - the main table in my database and a smaller table I imported. When the smaller table numbers exactly match the numbers in the main table, I need a checkbox in the main table checked. Here are the particulars:

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"

Thats it - I need the exact query written out using my table and field names so I can just paste it in with no syntax errors - I'm not a programmer.

Thanks very much to anybody who can help

Jim
0
hiviper
Asked:
hiviper
  • 6
  • 6
  • 3
1 Solution
 
rockiroadsCommented:
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
0
 
rockiroadsCommented:
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
0
 
hiviperAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jefftwilleyCommented:
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
0
 
hiviperAuthor Commented:
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
0
 
jefftwilleyCommented:
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
0
 
rockiroadsCommented:
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
0
 
jefftwilleyCommented:
Sometimes, it's the easy answer!

UPDATE tableA, tableB SET tableA.[true] = True WHERE (([tableA].[junk]=[tableB].[junk]));
J
0
 
hiviperAuthor Commented:
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
0
 
jefftwilleyCommented:
For the SEEK method,
Check your indexes. both tables. When you open the picfiles table in design view, what is the index set for?
open picfiles, click the Indexes button on the toolbar, (it has a little yellow lightning bolt), and look to see if it's named "primary index" or if it's more than one field. We need to match things up.

------------------------------------------------------------------------------------
In the mean time, a simple code to cut and paste is below.
 Make a form if you haven't already done so.
Create a command button.
On the On_Click event of the button, paste this code.
Press the button.
Check your table to make sure it updated.
If it worked like you wanted, then we can jazz it up some to return a message, or give you a count or something.

Dim strSQL as string

            strSQL = "UPDATE Inventory, picfiles SET Inventory.[pic] = True "
            strSQL = strSQL & "WHERE (([Inventory].[mfg part number]=[picfiles].[picnumbers]));"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL


Let us know if you're still getting errors.
J
0
 
jefftwilleyCommented:
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
0
 
hiviperAuthor Commented:
Thanks Jeff, you got the winner there, worked perfectly!

Jim
0
 
hiviperAuthor Commented:
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.
0
 
jefftwilleyCommented:
You're most welcome....and Rocki should get credit here too....he's a genious you know!! Hi Rock!!
J
0
 
hiviperAuthor Commented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now