MS Access query displays duplicate record that matches value in another column

I need help to query a table for distinct records by TRANS_CODE that matches value in the ITEM column. Any help would be very much appreciative.

Here is an example of the table.

ID   TRANS_CODE  ITEM  
1    CD50        Red  
2    TN30       Blue  
3    RC50       Green  
4    WC70       White  
5    PT30       Blue  
6    AB60       White  
7    RC50       Red  
8    WC70       Blue  
9    TN30       Green  
10   PT30       Green  

The logic for displaying duplicate TRANS_CODE is driven by the ITEM column. The first logic for duplicate TRANS_CODE is to show ITEM that is Blue and the secord is show ITEM that is Green. For example...

Duplicate TRANS_CODE that has ITEM, Blue, Green, and Red: Show record with Blue only

Duplicate TRANS_CODE that has ITEM, Green, Red, and White: Show record with Green only

The result should gene开发者_JS百科rate this list....

ID   TRANS_CODE   ITEM  
1    CD50         Red  
2    TN30         Blue  
3    RC50        Green  
8    WC70         Blue  
5    PT30         Blue  
6    AB60         White  

You can map your colors to Numbers using switch. Then using two inline queries joined together you can get the answer you're looking for.

Technically you don't need to use the switch because the colors are in alpha order but just in case your real values weren't

SELECT t.id, 
       t.transcode, 
       t.item 
FROM   (SELECT transcode, 
               item, 
               id, 
               Switch([ITEM] = "Blue", 1, [ITEM] = "Green", 2, [ITEM] = "Red", 3 
               , 
               [ITEM] = 
               "White", 4) AS weight 
        FROM   table2) AS t 
       INNER JOIN (SELECT transcode, 
                          MIN(Switch([ITEM] = "Blue", 1, [ITEM] = "Green", 2, 
                              [ITEM] = 
                              "Red", 3, 
                              [ITEM] = 
                                  "White", 4)) AS weight 
                   FROM   table2 
                   GROUP  BY transcode) AS t2 
         ON ( t.weight = t2.weight ) 
            AND ( t.transcode = t2.transcode ) 
ORDER  BY t.id 

This gives you the output below, I hope that ID 8 being after 6 instead of between 3 and 5 works for you

id  transcode   item
1   CD50    Red
2   TN30    Blue
3   RC50    Green
5   PT30    Blue
6   AB60    White
8   WC70    Blue

Note: you can replace MIN(Switch... with FIRST(Switch... which may improve performance

You should try this:

select 
   (select ID from sometable as t2 where t2.trans_code = t1.trans_code and t2.item = Min(t1.Item)),
   t1.trans_code,
   Min(t1.item)
from sometable as t1
group by trans_code 
order by 1

As you did not specify which color should have preference, I set the preference alphabetically (Blue, Green, Red and White). That's hat I could capture from your expected output.