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

Question

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
      endif
   next j
   if isin = false then
      A.OBE(i) = true
   end if
next i
End Function

Answer 1

Hi,

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.

Servus
Karl

---

Access News
Access DevCon

Answer 2

Hi,

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.

Servus
Karl
Access News
Access DevCon

Answer 3

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.*
FROM a INNER JOIN b
ON b.bid 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 FindWord.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!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.