0

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
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
mightymax
  • 431
  • 1
  • 5
  • 16

1 Answers1

1

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.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
  • 1
    Wow that's beautiful. Thank you – mightymax Sep 24 '19 at 21:32
  • is there a way to put a condition if t1 doesn't have t2.[Control Number] add t2.[ID], t2.[Current Status]? – mightymax Sep 24 '19 at 21:42
  • @MaxineHammett That would require a separate `insert into` (append) query, but would be better placed as a separate question, since this question concerns updating records with matching values. – Lee Mac Sep 24 '19 at 21:46