Want query to output a word instead of the field

[Guest]

I want a query that will output a word if a field is not null.

To be specific, I have (simplified down):
tblSCR tblDBMs_for_SCRs
------- ---------------------
SCR_ID SCR_ID
SCR_Element SCR_DBM_Actual
SCR_IR_Tag

I want to join the two tables and end up with a distinct list of Elements
that have an SCR_IR_Tag that matches the criteria I give and output the word
"Included" if the SCR_DBM_Actual is not null.

This is as far as I can get:
SELECT DISTINCT tblSCR.SCR_Element
FROM tblSCR LEFT JOIN tblDBMs_for_SCRs ON tblSCR.SCR_ID =
tblDBMs_for_SCRs.SCR_ID
WHERE (((tblDBMs_for_SCRs.SCR_DBM_Actual) Is Not Null) AND
((tblSCR.SCR_Multipurpose)="IR-11"));

I have done this in the past where one query output "Included" if the field
was not null, another query output the work "Planned" if the field was null,
and I used a union to put the two queries together--but I can't remember how
to get the query to output a word!

Does anyone know how to do this?

Thank you,
Judy

 

[Guest]

Judy:

There seems to be an inherent contradiction in your query as you are placing
an IS NOT NULL restriction on a column in the table on the right side of a
LEFT OUTER JOIN. This would in effect make the join an INNER JOIN. Quite
how you approach this depends on what's meant by 'if the SCR_DBM_Actual is
not null'. Does it mean where a matching row exists in the tblDBMs_for_SCRs
table but the SCR_DBM_Actual column is Null? Or does it take in that
possibility and the possibility that no matching row exists in the
SCR_DBM_Actual table? As you've used a LEFT OUTER JOIN I suspect the latter,
so on that basis you want to return "Included" if the column is not Null, and
"Planned" if there's a matching row and the column is Null or if there is no
matching row. You can use the IIF function to conditionally return the
relevant word, so the query would go something like this:

SELECT DISTINCT tblSCR.SCR_Element,
IIF(tblDBMs_for_SCRs.SCR_DBM_Actual IS NULL,"Planned","Included") AS Status
FROM tblSCR LEFT JOIN tblDBMs_for_SCRs
ON tblSCR.SCR_ID = tblDBMs_for_SCRs.SCR_ID
WHERE tblSCR.SCR_Multipurpose="IR-11";

If its only Nulls in matching rows which are relevant then all you need to
do is change it to an INNER JOIN.

Ken Sheridan
Stafford, England

 

[Guest]

Thank you very much for your reply. I have not used IIF in a function before
and appreciate your going the extra mile and giving me the example. Your
solution is much better than the Union query I was going to use. (I tested
it out and it works beautifully.)

It is amazing to me that someone in England helped me with a problem in
California.

Thanks again,
Judy

 

[Guest]

Judy:

Glad to be able to help. As it happens I have close ties with California.
I have a friend who lives in Newark, not far from San Francisco, who I help
out a lot with database stuff in her job with a federal government agency
there.

I also have a small family connexion with the US in that my grandmother was
brought up in Boston, Mass.

Ken Sheridan
Stafford, England