Popup List from Related Field

[shorty]

I have a table called Tenant, it has a field called Property that is a drop down value list which the values comes from the Properties table. In my table Tenant, I also have a field called Unit #, it is a look up field from Properties table. Now that would work normally except when I enter 1126 Brookeridge I would like the Unit # to show a list of the Units there (that would be 1 through 10) but instead it only shows me the first one. I know that when I specified my lookup it says that it will copy the first value from the related item. But I know I have seen other databases do this. Any help would be appreciated.

[bcooney]

You can keep Unit# a lookup if you want, but it will only display the first value it finds. What you really want is another popup menu on your UnitID field that has a value list of Units. This value list is a related value list using the relationship Property::Units by PropertyID.

Therefore, in the Tenant record you store the PropertyID and the UnitID.