Can one fill a Joined field with "Not Found" for joined table records?

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003

If two tables are joined, how can I have all records included from Db1 (fieldname = "Found_YN")
populated with either "Found" or "Not Found" depending whether their is a valid Join of related
fields in Db2?

FROM Db1 INNER JOIN Db2 ON Db1.MC=Db2.NEWMC

I am probably over thinking this. I may not have to Join the tables but not sure.


TIA EagleOne
 
What you need to use is a Left Outer Join (or simply "Left Join" without the
word "Outer"). It will give you all the record for Db1 plus the values for
the related fields in Db2 or Null is there is no related fields. All you
have to do after would be to use an IIF statement to output your "Found" or
"Not Found" values.

Possible problem: if you have more than a single related field in Db2 than
you will get duplicates for Db1; like with a regular Inner Join.

Another possibility would be to use the Exists or Not Exists statement
instead of a JOIN to determine the values "Found" or "Not Found" but the
syntax is a little more complicated than the other solution.
 
Oui, il y a en plusieurs: jetez un coup d'oeil sur les groupes avec .FR.
dedans; genre:

microsoft.public.fr.access
 
Syl,

FROM Db1 LEFT JOIN Db2 ON Db1.MC=Db2.NEWMC

Understand the IIF() in general.

That said, how do I do it in this instance?

Logically I think Found_YN = IIF(??? = Null, "Found", "Not Found")

What should "??? = Null" syntax be in this instance?

Also, how do I write the syntax in SQL view or is that possible?

Thanks EagleOne
 
Some attempts but no success:

SELECT FoundStatus = IIF(IsNull(Db2.[NEWMC]) = True,"Not Found,"Found")

FROM Db1 INNER JOIN Db2 ON Db1.MC=Db2.NEWMC
 
Replace "Inner Join" with "Left Join" and you will be OK.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Some attempts but no success:

SELECT FoundStatus = IIF(IsNull(Db2.[NEWMC]) = True,"Not Found,"Found")

FROM Db1 INNER JOIN Db2 ON Db1.MC=Db2.NEWMC


What you need to use is a Left Outer Join (or simply "Left Join" without
the
word "Outer"). It will give you all the record for Db1 plus the values
for
the related fields in Db2 or Null is there is no related fields. All you
have to do after would be to use an IIF statement to output your "Found"
or
"Not Found" values.

Possible problem: if you have more than a single related field in Db2 than
you will get duplicates for Db1; like with a regular Inner Join.

Another possibility would be to use the Exists or Not Exists statement
instead of a JOIN to determine the values "Found" or "Not Found" but the
syntax is a little more complicated than the other solution.
Click to expand...
Click to expand...
 
Syl,

I get a "missing operator" when I use Found_YN = IIF(IsNull(Db2.[NEWMC2]) = True,"Not Found,"Found")

Specifically:

SELECT Field1, Field2, Found_YN = IIF(IsNull(Db2.[NEWMC2]) = True,"Not Found,"Found"), Field3,
Field4, etc.

My SQL syntax is incorrect. Thoughts?

EagleOne
 
Hum, with JET, it looks like that you must put the alias Found_YN after the
expression:

SELECT Field1, Field2, IIF(IsNull(Db2.[NEWMC2]) = True,"Not Found,"Found")
as Found_YN, ...

Also, IsNull is already a logical expression; so you don't have to add the
test « = True » but this is not a real error here.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Syl,

I get a "missing operator" when I use Found_YN = IIF(IsNull(Db2.[NEWMC2])
= True,"Not Found,"Found")

Specifically:

SELECT Field1, Field2, Found_YN = IIF(IsNull(Db2.[NEWMC2]) = True,"Not
Found,"Found"), Field3,
Field4, etc.

My SQL syntax is incorrect. Thoughts?

EagleOne

Replace "Inner Join" with "Left Join" and you will be OK.
Click to expand...
Click to expand...
 
Back
Top