Forum Discussion
IIF Statement for a 1:M
Hi, First time poster:
My data table has a 1:M relationship and I want to qry the highest level of 'color'
Red is Highest; Green Lowest
ID Color
1 Red
1 Green
1 Yellow
2 Yellow
3 Green
3 Yellow
4 Red
4 Yellow
My results should look like this:
1 Red
2 Yellow
3 Green
4 Red
I have tried using an IIF statement but can't seem to get it working.
Suggestion?
you create a table (on demo ColorT) and input the colors in Ascending orders.
then create a Query (FinalQuery) that will output your result.
see the attached demo
- I think the result is different:
1 Red
2 Yellow
3 Yellow
4 Red you create a table (on demo ColorT) and input the colors in Ascending orders.
then create a Query (FinalQuery) that will output your result.
see the attached demo
- Thanks arnel_gp
I had thought of that, but really just wanted to run in a single sort. I'll keep plugging- I didn't review Arnel's suggestion, but I'm sure it's the most appropriate. Table driven solutions (like the ColorT with an explicit designation of the ranking of colors) tend to be the most efficient.
Keep in mind that there is no inherent way to rank colors. That's an entirely subjective evaluation and subject to change at any time. Today you want Red to be highest, but there's no logical reason it can't become the lowest ranking as a result of some sort of change to your business rules. Don't give up on a good solution while searching for a chimerical one.
