Newbie design problem
I manage an office building full of suites and each suite has multiple rooms. I have constructed a table with the fields "suite number," ‘room number," and a unique “ID number” for each room to use as a primary key, as room numbers repeat in multiple offices.
The rental staff provides me with a constantly changing list of who the tenants are. I can turn that in to a table with the fields “Tenant name, “Suite number”, and”Room Number”. I would like to add the “ID number” field to this table as well.
How do I set it up so that this second table takes the suite and room numbers, looks them up in the first table, and then and inserts the corresponding room ID numbers in this table?