Could not add the table....

[rpadminserver]

Hello Friends.
I'm trying to put a IIF statement in my access SQL
Here is the statement

select a.id,a.mfr
iif(a.mfr !=(select alias from propermfr),"CHECK MFR",0)as REASON
from test4a a

I keep getting "Could not add the table propermfr"

Help please

 

[Joe4]

I am not sure what you are trying to do, or the relationships, but it appears that you have two tables (test4a, propermfr). If that is the case, then join them in your query first before you start doing calculations on their fields.

 

[rpadminserver]

What I'm attempting to do is see if A.MFR is in the list of ALIAS that I'm pulling from propermfr.

There is no join to be made.

If I was doing this in real sql (not this access version of sql) i would write it like this:

selext a.id, a.mfr,
(case when a.mfr not in (select distinct alias from popermfr) then "CHECK MFR" else null end) as reason
from test4a a

 

[Joe4]

There is no join to be made.

This could be done using joins, i.e.

SELECT DISTINCT 
   mfr.ID
   , mfr.mfr
   , IIf(IsNull([propermfr]![alias]),"Check mfr","") AS reason
FROM 
   mfr 
LEFT JOIN 
   propermfr ON mfr.mfr = propermfr.alias;