Have query return yes or no

[Mike Hughes]

Is there a way to have this query insert a "Y" in the EMPLOYMENT_HISTORY.EMPLOYER_ID if there is an EMPLOYMENT_HISTORY.EMPLOYER_ID

and a "N" in the EMPLOYMENT_HISTORY.EMPLOYER_ID
if there is no EMPLOYMENT_HISTORY.EMPLOYER_ID ?

Thanks,

SELECT
[1 ALL OPEN CASES].DO,
[1 ALL OPEN CASES].WORKER,
[1 ALL OPEN CASES].CASE,
NOLDBA_INT_CASE_MEMBER.MEMBER_ID,
NOLDBA_CASE_ROLLUP.DT_LAST_PAY,
Sum(NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED-NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID) AS OWED,
NOLDBA_INT_EMPLOYMENT_HISTORY.EMPLOYER_ID


FROM (([1 ALL OPEN CASES] INNER JOIN NOLDBA_INT_CASE_MEMBER ON [1 ALL OPEN CASES].CASE = NOLDBA_INT_CASE_MEMBER.CASE_ID) INNER JOIN NOLDBA_INT_EMPLOYMENT_HISTORY ON NOLDBA_INT_CASE_MEMBER.MEMBER_ID = NOLDBA_INT_EMPLOYMENT_HISTORY.MEMBER_ID) INNER JOIN NOLDBA_CASE_ROLLUP ON [1 ALL OPEN CASES].CASE = NOLDBA_CASE_ROLLUP.ID_CASE

WHERE
(((Date()-[NOLDBA_CASE_ROLLUP].[DT_LAST_PAY])>[NO PAY IN]) AND
((NOLDBA_INT_CASE_MEMBER.RELATION_CODE)="A") AND
((NOLDBA_INT_EMPLOYMENT_HISTORY.DATE_END)=#12/31/9999#) AND
(([NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_OWED]-[NOLDBA_CASE_ROLLUP].[LIFE_TO_DATE_PAID])>[GREATER THAN $]))


GROUP BY [1 ALL OPEN CASES].DO, [1 ALL OPEN CASES].WORKER, [1 ALL OPEN CASES].CASE, NOLDBA_INT_CASE_MEMBER.MEMBER_ID, NOLDBA_CASE_ROLLUP.DT_LAST_PAY, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_OWED, NOLDBA_CASE_ROLLUP.LIFE_TO_DATE_PAID,
NOLDBA_INT_EMPLOYMENT_HISTORY.EMPLOYER_ID


HAVING ((([1 ALL OPEN CASES].DO)=[DISTRICT]));

 

[pbaldy]

Try

IIf(IsNull(EMPLOYMENT_HISTORY.EMPLOYER_ID), "N", "Y")

 

[boblarson]

You can't use the same name for the field. You have to slightly modify it in order to use an IIF statement:

IIf(IsNull([EMPLOYMENT_HISTORY].[EMPLOYER_ID]), "N", "Y")  As EmployerID