In ArcGIS (ArcMap 10.8 or Pro 2.6) I have a table of 4000 buildings (rows). Each row/building belongs to one of fifty groups and thus has a group ID of 1-50 in the field GROUP_ID. Each row also has a GROUP_STATUS field which can have the value of "fully open" or "partly open".
Some groups have conflicting statuses. For example, there are multiple rows in Group_ID = 10, 14, or 40 which have values of both "partly open" and "fully open" in GROUP_STATUS. For these, and all other groups that have the same issue, I need to change the status to only "partly open". I need to find out for which GROUP_IDs this is the case so I can update the status accordingly.
Without doing a frequency table - only a selection or definition query - how do I identify all GROUP_IDs that have different values for GROUP_STATUS in the table?
I would like to find rows where GROUP_ID = a single value and GROUP_STATUS = more than one value across the rows, along the lines of "select by GROUP_ID where GROUP_ID is consistent but GROUP_ID is not consistent."