HOWTO: indicate if a value exists in another table

[gebli]

Hi - Just found this forum and it looks like it is what I was needing.

I am trying to move work from Excel to Access and don't know how to replace the vlookup function...

Scenario is quite simple:

Table A: (Name, Role)
Table B: (Type, Role)

I want to create a query that would indicate if a role in table A is valid or not depending on its existence in table B.

E.g.
Table A:

John, Engineer
Paul, Musician
George, "none selected"
Ringo, Doctor

Table B:
xxx, Engineer
yyy, Doctor

The output would look like:

John, Engineer, Valid
Paul, Musician, Invalid
George, "none selected", Invalid
Ringo, Doctor, Valid

It may be quite easy to do it but my sql skills are still quite rusty!

Thanks much.

Gerry

 

[pbaldy]

Welcome to the site. Untested, but try

SELECT A.Name, A.Role, IIf(IsNull(B.Role), "Invalid", "Valid") AS TestField
FROM TableA AS A LEFT JOIN TableB AS B ON A.Role = B.Role

 

[gebli]

Yes, it does work perfectly. Thank you very much.
I am not quite sure to understand the logic behind it. Let me try...
The LEFT JOIN will bring A.NAME but since it will not find a matching B.Role it will leave it blank (or null) - though still displaying the A.Role.

Am I right?
Gerry

 

[pbaldy]

Exactly, yes. The LEFT JOIN will return all records from the left table, whether there's a matching record in the right table or not. When there's not, it returns Null, which we account for in the IIf() function.

 

[gebli]

Just brilliant - Thanks a lot for the solution and its explanation.

Gerry

 

[pbaldy]

No problem, Gerry.