And an employee can be issued up to 3 different keys? What will you do if changes are made and an employee needs more than three?
I bring this up because you can set up your tables/relationships differently and be able to issue as many keys to a given employee as needed and not be limited to any arbitrary number of keys.
And your screen shot indicates that only one employee can be assigned to a given room and they can only be assigned to one such room. That seems unlikely to be the case given most "cubicle" arrangements used these days.
What sparks that comment is this relationship:
Personnel Records::__kP_Employee ID = BuildingInformation::__KP_Room Number
That can't be right. Either you put the EmployeeID in both of these fields or you put the room number in both of these fields, but not both. And one of these fields will be a primary key (__KP) and one is a foreign key (_KF).
Compare these three options:
Personnel Records::__kP_Employee ID = BuildingInformation::_KF_Employee ID
This assumes that one employee can be assigned to more than one room
Personnel Records::_kF_Room Number = BuildingInformation::__KP_Room Number
This assumes that one room can be assigned to more than one employee
and you might even need:
Personnel Records::__kP_Employee ID = Person_Room::_KF_Employee ID
BuildingInformation::__KP_Room Number = Person_Room::_KF_Room Number
This allows you to assign multiple employees to the same room but also to be able to assign the same employee to more than one room.
You'll need to figure out which of these will work for you.
To get the list you've requested, you can use the List function in a calculation field: List ( Key1 ; key2 ; key3 ) and then you can set your value list to draw values from the calculation field, but I wouldn't set it up that way. I'd link a table of keys to the Building information table instead of using separate fields for the keys like you have.
This is great, thanks. Sorry about the term "Key", I relaize what you mean now that you said it.
Perosnnel here refers to Teachers and Paraprofessionals. Each teacher is assgned one classroom. The para's do not get a classroom key. I suppose it is possible that a staff (teacher or administration) could be assigned more than three keys. One classroom may have 1 teacher and a few paras.
Does this change how you would answer the question?
When you said "link a table of keys to Building Information Table" did you mean create a table just to for all of the keys in the building? If so then I could set up a drop-down in Building Information for each of the Keys assigned (Key 1, Key 2, Key 3), correct?
That's the idea as you are now listing data from a single field so it's less complex. And you can use a conditional value list to list only keys for a specific room in the value list.