Macro to check if a shared field exists between 2 different tables?

Sanchez, Bijan E (US) 1 Reputation point

I have 2 tables "A" and "B" in an Access database. There's a column in A called "AID" with an ID number, and there's a column in B called "BID" with an ID number.
Also, there's a column in A called "OBE" (for obsolete). OBE is a boolean True/False data type.

I want to write a macro that iterates through each ID number in AID and checks if it exist in BID. If an ID number in AID doesn't exist in BID, I want OBE to be set to "True" for that row.

I plan to implement this macro into a button on a form.

I think I have the right idea below but I am unfamiliar with VBA/Access syntax.

Edit (additional context):
One of the reasons why I have it as a macro and arrays is because BID isnt just an ID, its a long title that contains the ID somewhere inside of it. The ID exists somewhere in BID after a sequence of words, so basically I was planning to additionally use the "left()" and "right()" functions, pin-pointing the ID, then creating each element of BID_array one-by-one in a for-loop.

Public Function
dim AID_array as array
dim BID_array as array
dim AID_length as integer
dim BID_length as integer
isin as boolean

AID_array = A.AID
BID_array = B.BID
AID_length = length(AID_array)
BID_length = length(BID_array)

for i = 1:AID_length
   isin = false
   for j = 1:BID_length
      if AID_array(i) = BID_array(j) then
      isin = true
   next j
   if isin = false then
      A.OBE(i) = true
   end if
next i
End Function
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
853 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Karl Donaubauer 1,726 Reputation points MVP


    Are you sure you want to do this by code? Usually this would be done by a query. There's even a query wizard that helps you create the query if you go to the ribbon and choose
    Create - Query Wizard - Find Unmatched Query Wizard

    This query will find the records that are in A but not in B. If you then change the query type from Select to Update you should be able to reach your goal in a more efficient and performant database way than programming it on your own.


    Access News
    Access DevCon

  2. Karl Donaubauer 1,726 Reputation points MVP


    If you can do the extraction of the ID in an expression of a query then you should be able create a first query to do just this and then use this query instead of table B in the already described update query.

    Access News
    Access DevCon

  3. Ken Sheridan 2,756 Reputation points

    If you simply want to find rows in B which contain the AID value as a substring within the BID column in B then you can use the LIKE operator to join the tables:

    SELECT b.*
    ON LIKE "" & a.aid & "";

    However, this might produce specious substring matches. If the ID value in BID is separate 'word' in the string, i.e. it is separated from rest of the string by spaces, punctuation characters etc. then you might like to take a look at in my public databases folder at:!169

    This little demo file illustrates the use of a FindWord function, and uses the text of T S Eliot's poem The Wasteland as its example. If I add a table named Words with the values summer, Marie, and bridge in a single column named Word, the following query using the LIKE operator

    SELECT Wasteland.*
    FROM Wasteland INNER JOIN Words
    ON Wasteland.LineText LIKE "" & Words.Word & "";

    returns 6 matching rows:

    Summer surprised us, coming over the Starnbergerse
    And I was frightened. He said, Marie,
    Marie, hold on tight. And down we went.
    A crowd flowed over London Bridge, so many,
    Or other testimony of summer nights. The nymphs are
    London Bridge is falling down falling down falling

    The following query calling the FindWord function returns the same 6 rows:

    SELECT Wasteland.*
    FROM Wasteland INNER JOIN Words
    ON FindWord(Wasteland.LineText,Words.Word);

    So, in this case the LIKE operator works correctly, with no specious matches, but the second query is still a safer option.