Access Lookup - pull thru of data from associated/linked table - data from two fields

Richievo 

Unfortunately, you've implemented Lookup fields in your tables. That "feature" is available for reasons that are less than convincing to experienced Access developers. They tend to confuse new users, at the very least, and often throw complications into the situation. They are unique to Access and SharePoint lists. No other database engine uses them. 

The problem you have encountered is common to attempts to use Lookup Fields in tables instead of creating valid Primary/Foreign Key relationships between tables in the Relationship Diagram. 

My advice would be not to continue fighting with attempting to make it work. Convert them to standard Long Integer fields and designate the relationships using the standard method -- the Relationship Diagram. 

That way "what you see is what you get" in the queries, forms and reports where you use the tables. This confusion over the visibility of the Foreign Key versus the text value associated with that Foreign Key will be eliminated.