IF query

[AJ IT Student]

hiya..
prob my last post on the project i'm doing..
but just to finish it off (I'm just finalising the last report!!), I'm trying to do a nice IF query.

Its simple logic..
basically its a textbox, and depending on the value of the variable 'Performance' I'd it to say different things.

IF Performance = "excellent" Write in textbox "reasonable pay increase" OR IF Performance = "good" write in textbox "increase wage" OF IF Performance = "satisfactory/poor/very poor" write in textbox "Provide Performance incentive"

I need to know how to get this to work

many thanks
aj

 

[Rich]

Look up nested Iif, and or the Switch function

 

[raskew]

In terms of the shortest possible solution, I'd opt for a combination of the Switch() and Choose() functions. Say, for example, you have a table that looks like this:

[b]
PerformanceID	EmployeeID	Performance[/b]
1	               1	Excellent
2	               2	Good
3	               3	Satisfactory
4	               4	Poor
5	               5	Very Poor

...you could employ the Switch() function thusly

SELECT tblPerformance.EmployeeID, tblPerformance.Performance, Switch([Performance]="Excellent",1,[Performance]="Good",2,InStr("Satisfactory Poor Very Poor",[Performance])>0,3) AS x,

...and the Choose() function in this manner

Choose([x],"reasonable pay increase","increase wage","Provide Performance incentive") AS Response
FROM tblPerformance;

Tie it all together and it looks like this:

SELECT tblPerformance.EmployeeID, tblPerformance.Performance,
Switch([Performance]="Excellent",1,[Performance]="Good",2,
InStr("Satisfactory Poor Very Poor",[Performance])>0,3) AS x, 
Choose([x],"reasonable pay increase","increase wage",
"Provide Performance incentive") AS Response
FROM tblPerformance;

...and results in this:

[b]
EmployeeID	Performance	x	Response[/b]
1	        Excellent	1	reasonable pay increase
2	        Good	        2	increase wage
3	        Satisfactory	3	Provide Performance incentive
4	        Poor	        3	Provide Performance incentive
5	        Very Poor	3	Provide Performance incentive

This is just one of many ways to go about it. You could, alternatively, used nested Iif() statements, just the Switch() function, or a Select Case scenario.

Please post back (in this thread, not a new one where an interested viewer is forced to track down your previous questions and the responses they elicited).

Bob