I have a large database with multiple tables linked to each other.

I want to be able to have a table that grabs a field from another table based on a value in the table

table dbo_str has a field STR_NBR and STR_ST
table Store_Specifics has a field Store I want to add a field called State that would automatically pull the state from dbo_str based on the store

table dbo_str is a linked ODBC table from a another database on a different machine on my network.

Thanks in advance.