CODEWITHSUNDEEP
ms-accessiif
Rashid Abib
Rashid Abib

Reputation: 189

IIF When Using Similar Criteria

Their are 2 different jobstatuses that I want to display the same way, my IIF statement changes the display, BUT I would like for only one entry to be returned for both. For example, I want it to show like this

JobStatus
---------
Inactive
Let Go

But the returned result set I get is

JobStatus
---------
Active
Inactive
Let Go
Let Go

What do I need to change in my query to make it show like I need? (and I need this to be done via a query not vba)

jobstatus1: IIf([jobstatus]="Terminated","Let Go",IIf([jobstatus]="Fired","Let Go",[jobstatus]))

EDIT -- Full Syntax. The IIF statement I need to use is for the alpha.jobstatus line right after the Select

SELECT alpha.jobstatus, Count(beta.ID) AS CountOfID
FROM alpha LEFT JOIN beta ON alpha.jobstatus = beta.jobstatus
GROUP BY alpha.jobstatus, alpha.order
HAVING (((alpha.jobstatus) Not In (Select [jobstatus] From tbl_Valid)))
ORDER BY alpha.order;

Upvotes: 1

Views: 129

Answers (1)

HansUp
HansUp

Reputation: 97100

Use SELECT DISTINCT to return only distinct values.

Also you can use a simpler expression for that calculated field --- one with a single IIf instead of nested IIf's. Switch your query to SQL View and revise the start of the statement text to this ...

SELECT DISTINCT IIf([jobstatus] IN ('Terminated', 'Fired'), 'Let Go', [jobstatus]) AS jobstatus1

Upvotes: 1

Related Questions