Action Query Question

[dargo72]

It's been a while since I've created a true action query and I need help.



I have one table with two fields.

Depending on the number value in field 1, I want to make field 2 contain text for a color (Red, Yellow, Green).

I set the criteria for the number values in field 1 (<=2 , >2 and <5 , <5)

I am not sure how to run the query so that deppending on the number in that field, field 2 is automatically filled in with a color (text).

Any help is appreciated.

[JoeM]

Why do you want to try to save it on the table level?

Typically, table fields should never be dependent upon other fields, meaning you should never store anything in a table which can be dervied or calculated from other fields.
It undermines the dynamic nature of a database and can destroy data integrity.
You just use a query to calculate it, and you can use the query as your Control Source for whatever it is you are trying to do.

[dargo72]

Why do you want to try to save it on the table level?

Typically, table fields should never be dependent upon other fields, meaning you should never store anything in a table which can be dervied or calculated from other fields.
It undermines the dynamic nature of a database and can destroy data integrity.
You just use a query to calculate it, and you can use the query as your Control Source for whatever it is you are trying to do.

Thank you for the feedback. So I need to create a query that will return a color value based upon a number value. I'm not sure exactly where to start with that. Suggestions/Help are appreciated.

[JoeM]

It sounds like you need a nested IIF statement, i.e.

MyColor: IIF([Field1]>5,"Green",IIF([Field1]>2,"Yellow","Red"))

adjust to suit your needs.

[dargo72]

It sounds like you need a nested IIF statement, i.e.

MyColor: IIF([Field1]>5,"Green",IIF([Field1]>2,"Yellow","Red"))

adjust to suit your needs.

Thank you. I entered:

Color: (IIf([Degree of Impact]<3,"Green"),IIf([Degree of Impact]=3 Or 4,"Yellow"),IIf([Degree of Impact]>4,"Red"))

However, I gety an error message that "Syntax Error (comma) in query expression.

[JoeM]

Try again, using the same logic/format I used in my first thread (you only need two IIFs to do it this way)

MyColor: IIF([Degree of Impact]>4,"Red",IIF([Degree of Impact]>=3,"Yellow","Green"))

It is important to understand a few things about IIF statements:
1. The IIF statement has three arguments:
IIF(condition, what to return if condition is true, what to return if condition is false)
2. If you have a nested IIF statement, working from left to right, once a condition is met, it will stop there and not look at the rest.

So, if we follow the logic of my statement above, here is what it is doing:
- First checks to see if "Degree of Impact" is greater than 4, and if it is, it will return "Red".
- If it is not, it looks at the second condition to see if it is greater than or equal to 3, and if it is it will return "Yellow"
(there is no need to check to see if it is also less than 4 here, because if it is greater than 4, the first condition would have picked it up).
- if it is not greater than or equal to 3, we know that is must be less than 3, so we don't even need to check to see if it less than 3 (because we know by process of elimination).
So we can simply tell it to return "Green" if neither of the first two conditions are true.

As long as you always order you conditions to work in increasing order (or decreasing order), you can use this type of logic and not have to explicitly check to see if it is between two values (unless you have gaps that would want ignored).

Make sense?

[dargo72]

Try again, using the same logic/format I used in my first thread (you only need two IIFs to do it this way)

MyColor: IIF([Degree of Impact]>4,"Red",IIF([Degree of Impact]>=3,"Yellow","Green"))

It is important to understand a few things about IIF statements:
1. The IIF statement has three arguments:
IIF(condition, what to return if condition is true, what to return if condition is false)
2. If you have a nested IIF statement, working from left to right, once a condition is met, it will stop there and not look at the rest.

So, if we follow the logic of my statement above, here is what it is doing:
- First checks to see if "Degree of Impact" is greater than 4, and if it is, it will return "Red".
- If it is not, it looks at the second condition to see if it is greater than or equal to 3, and if it is it will return "Yellow"
(there is no need to check to see if it is also less than 4 here, because if it is greater than 4, the first condition would have picked it up).
- if it is not greater than or equal to 3, we know that is must be less than 3, so we don't even need to check to see if it less than 3 (because we know by process of elimination).
So we can simply tell it to return "Green" if neither of the first two conditions are true.

As long as you always order you conditions to work in increasing order (or decreasing order), you can use this type of logic and not have to explicitly check to see if it is between two values (unless you have gaps that would want ignored).

Make sense?

Worked Perfectly, Thank you!