I am very new to Access so I apologize. I have a personnel table for all personnel within my Squadron (I'm in the Air Force). I want to create a lookup field with a dropdown to select the persons direct supervisor. This list needs to be all the people in the same table which it doesn't like. Does The Supervisor field have to be in a different table?
I'd also like to filter out in the dropdown list anyone who is a lower rank than the person I'm trying to select the supervisor of. I created a table for ranks and assigned each rank a number. So basically if the person is a SSgt and is assigned number 5, then filter out all person except those with a rank of 1-5 (sometimes someone will be the supervisor of another person of the same rank).