I did a lot of FileMaker work about 18 years ago but have been stuck in Access since then. Now I am trying to reproduce the functionality of a database I have in Access.
This part of it is an inventory, keeping track of which lab computers are where. I have a table with computers, and one with desks, and then one where computers are assigned to desks, with fields for the start and end dates of the assignment (so we can maintain the history of where they have been).
When recording a new assignment, I would like to have dropdowns for both the computer and desk fields which shows only those computers/desks which are not currently in an active assignment (end date is still blank). But I can't figure out how to create a value list based on a filter of another field in the same table.