IIF Statement for a 1:M

New Contributor

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.





4 Replies
I think the result is different:

1 Red
2 Yellow
3 Yellow
4 Red
best response confirmed by BettyBike4731 (New Contributor)


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.