If/Else Update Query

[cazziewhelan]

Hi All,

Hope someone can help, its been years since ive used SQL and now trying to remembered in college a few years ago!

I am building a database to measure performance in business, based on targets and actual figures. There is a points system for those whos actual is equal or above there target figure. I want to build an update query, that will run and calculate points once actual figures are added to the database. I have managed ot write 2 seperate queries, one to update with points when hey exceed target and one to award no points if the exceed below the taret, but how can I combine these 2 queries as one, with an if else structure?

To update when they exceed target


Here are there queries I have working so far:

UPDATE Result_Details SET Result_Details.Result_Value = "60"
WHERE (((Result_Details.Actual_Value)>=[Target_Value]) AND ((Result_Details.KPI_Name)="Productivity"));

UPDATE Result_Details SET Result_Details.Result_Value = "0"
WHERE (((Result_Details.Actual_Value)<=[Target_Value]));

Also, I have stored the point values in another table, and called them KPI_Pass and KPI_Fail, and tried to pass these paramaters into this query instead of the 60 value, but I was getting syntax error!

Hope someone out there can help!

[orange]

You are setting your values to strings "60", not number 60 (no quotes).

Wouldn't this AND ((Result_Details.KPI_Name)="Productivity") apply to both queries you currently have?

Air code untested and assuming numeric values and you are always dealing with KPI_Name ="Productivity"

UPDATE Result_Details 
SET Result_Details.Result_Value = IIf (Result_Details.Actual_Value)>=[Target_Value], 60,0)
WHERE  ((Result_Details.KPI_Name)="Productivity")
 

As I said Untested.