Creating a filtered value list to use across an entire database
I use a database to manage data about residents in a boarding house, and I'd like to create a conditional value list that contains only current residents, which I could then use for data entry in multiple tables (room occupancy, chores, etc.).
I have one primary People table, with ID (serial number) and Name fields. I have a separate Residencies table, with a PersonID field linked to the People::ID field, as well as a "ResidentStatus" field ("Current" or "Former").
Can anyone explain how to generate the value list I'm looking for? I'm happy to provide more information if needed.