IIF Statement for a 1:M

[BettyBike4731]

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?

[#2]

I think the result is different:

1 Red
2 Yellow
3 Yellow
4 Red

[#3 · Accepted solution]

@BettyBike4731 

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

[#4]

Thanks @arnel_gp

I had thought of that, but really just wanted to run in a single sort. I'll keep plugging

[#5]

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.