Create a List from multiple fields
I am setting up a layout that has an employee id number (Table: Personnel Records) as the unique identifier and that has three fields (Key 1, Key 2, Key 3) based on the room assigned to that employee. Room Assignment is a drop-down list (Related Table: Building Information). In the Building Information Table the room number is the unique identifier and each room can have as many as 3 different keys per room. I currently have the fields for the keys per room set as Key 1, Key 2, and Key 3.
I would like to find a way for each of the fields (Key 1, Key 2, Key 3) in the layout from the Personnel Records to be a drop-down list that is a culmination three fields (Key 1, Key 2, and Key 3) in the Table: Building Information.
I apologize that the fields from both tables have the same name, it's easier for me to track.
The screen shot attached has the tables, the third table was an attempts at trying to create a repository of all keys to make the drop-down list from. I am open to any and all ideas.