Finding the 'Status' of a grouped record with multiple records - Microsoft Community

sorry if subject little confusing, wasnt sure best way phrase it. why cant find answer question. here's i'm trying do: 

i have tables joborders, positions , status. there can multiple positions per joborder.  each position has status (hot, confidential, normal, hold, or closed). on form (joborderf) joborder records shown , there subtable positions joborder listed.

within joborderf want have field status , postable. status should name status of position joborder grouped. example: 

joborder 1 has 3 positions (1=hold, 2=hot, , 3=closed)
job order 2 has 2 positions (1=closed, 2=normal)

joborder 3 has 2 positions (1=confidential, 2=closed)

i want status field find relevant status of positions, so: 
joborder 1 should list "hot"
joborder 2 should list "normal"

joborder 3 should list "confidential"

the preference of order listed within each joborder should be:  confidential, hot, normal, hold, , closed

then postable field either list status or if "hot" or "normal" should display "yes".

my problem cant figure out how have status display 1 option grouped joborder, explained above. cant use grouped max or last functions since max/last position wont correct answer. possible or reaching beyond access abilities?

the first thing create small lookup table status values column "preference". return min or max of related records.

tlkpstatus

pref   status

1       confidential

2       hot

3       normal

4       hold

5       closed

Office / Access / Windows 10 / Office 2010