Conditional Lists

Hi.

Im looking to create lists that are based upon other conditions in the database.

My scenario is that I'm developing a database that involves tracking guests and rooms.

My request is to approach the same problem from two different angles. I have guests that need to be allocated rooms.

I want to be able to bring up a list of clients and have a drop-down box of available rooms (rooms not already not allocated).

Second half of the problem is to bring up a list of occupied rooms and then drop down a list of guests to assign them to - guests that arent already allocated a room.

Part B of this problem is to have it so that I can swap guests in specific rooms. eg A list of all 20 rooms, and then literally move a client from one room to another somehow.

Im not sure how to do this.

I know its not bigger than Ben Hur.

Cheers,

Greg

PS: If this question is too big for a forum question, Im prepared to pay somebody to write this bit for me. I will send what database I have to insert the changes into directly.