Search code examples
ms-accessdcount

How to check if value exist in two tables

Hi I've searched the net and couldn't find the right answer to my question.

I have two tables GCSALLDATA and GCS-RECONCILED. They both have the same columns but I only need to focus on two [Control Number] (Short Text) and [NotInDevTrack] (yes/no)

I want to search GCSALLDATA for the Control Number found in GS-RECONCILED. If it's found Update the record.

From what I read using a DCOUNT should be able to do this, but wondered how would it cycle through all the records in the table?

Here is my attempt at using to use it

If DCount("[Control Number]", "GCSALLDATA", "Control Number=" & [GCS_Reconcile].[Control Number]) > 0 Then
    MsgBox ("Control number already in use")
Else
    MsgBox ("Control Number missing add it")
End If
Solution

  • Assuming I've correctly understood what you mean when you state:

    I want to search GCSALLDATA for the Control Number found in GS-RECONCILED. If it's found Update the record.

    You can use a simple update query to update records in GCSALLDATA for which there is a matching Control Number in GS-RECONCILED, for example:

    update GCSALLDATA t1 inner join GS-RECONCILED t2 on t1.[Control Number] = t2.[Control Number]
set t1.NotInDevTrack = True

    Assuming that you wish to set the NotInDevTrack field to True (Yes) for those numbers which match.