CODEWITHSUNDEEP
sqlms-access
Bijan Sanchez
Bijan Sanchez

Reputation: 39

SQL code to check if a field in one table exists in another table in Access

I have 2 tables "A" and "B" in an Access Database.

There's a column/field in A called "A_ID" with an ID number, and there's a column in B called "B_name" with a name (the name is a short text that could contain an ID number from A_ID)

Also, there's a column in A called "OBE" (for obsolete). OBE is a boolean True/False data type.

I want to write an Update Query with SQL that looks at each ID number in A_ID and checks if it exist somewhere in each B_name of table B. If an ID number in A_ID doesn't exist in any B_name, I want OBE to be set to "True" for that row.

(Each ID number is exclusive and would not exist in multiple B_names)

I hope to make the Update Query run upon clicking a button on a form, or does an Update Query automatically update?

This is what I have so far, and it returns "Syntax error in FROM clause":

SELECT *
FROM A
JOIN B
WHERE LOCATE (A.A_ID, B.B_name) > 0
SET A.OBE = True

I've also tried:

SELECT *
FROM A
JOIN B
WHERE A.A_ID LIKE CONCAT ('%', B.B_name, '%')
Set A.OBE = True

Edit (added context per June's comment):

Thanks for the comment, I agree. So the A_ID numbers look like "S5490", "S5491", "S5491R1", "S5491R2", "S5491R3", "S5492", "I5490", "I5491", etc. etc.

So basically they start with an S or I, have 4 digits, then they could have revisions at the end: "R1", "R2", etc.

The 4-digit number doesn't repeat unless there's a new revision (R) or if the ID starts with a different letter (S or I)

The B_name column varies a lot but it would look something like "Super Cool Product #S5479 Yeah!" or "Super Cool Product-S5479R1-Yeah!" and the text that appears before/after the ID within in the name would never resemble any other ID number and would never repeat an ID number

Upvotes: 0

Views: 986

Answers (1)

June7
June7

Reputation: 21379

Apparently these tables do not have a relationship and a direct JOIN is not possible. Without JOIN ON clauses, resulting dataset will be a Cartesian output and cannot be edited. Be aware Cartesian output can perform very slowly with large dataset. Consider:

Query1:
SELECT A.A_ID, B.B_name, InStr([B_name],[A_ID]) AS C FROM A, B;

Query2 (assuming A_ID is a text field):
UPDATE A SET OBE = Nz(DSum("C","Query1","A_ID='" & [A_ID] & "'"),0) = 0;

However, use of InStr() might produce undesired results. If A_ID is a number type, value of 1 will match to A1A, A12A, A111A, etc.; if text then value of James will match to Carla Jameson, Jamestown Ave, etc.

Upvotes: 1

Related Questions