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

Richievo 

You MUST enforce Referential Integrity to have a really solid relational database application.
I see several problems which are relics of the misbegotten Lookup fields. First, relationships MUST be on the Primary Key in the parent table (one side) and the corresponding Foreign Key in the child table (many side).

For example, the primary key in Programs by lender is LenderProgramID, as designate by the Key icon in the diagram. That needs to be related to a corresponding ID field in the CriteriaByLenderProgram table, I think. One Program has many criteria, right?
So, instead of duplicating the lender name and lender program in Criteria by Lender Program (an artifact of implementing the Lookup Fields), you have ONE field in CriteriaByLenderProgram, probably called LenderProgramID as well. The relationship is on the LenderProgramID Primary Key in ProgramsByLender and the LenderProgramID in CriteriaByLenderProgram. Remove the superfluous Lender Name and Program fields in the Criteria table. When you need to DISPLAY them, the join on the two tables always returns those text values from the one-side table.

I suspect the other join lines in your diagram are based on the same type of misshapen relationships. LoanCategoryID in the LoanCategoryTypes table (One Side) relates, not to the text value in "CategoryType" in ProgramsByLender (Many Side) and it should be LoanCategoryID as well, a Long Integer datatype in both places.

What matters most in a relational database application is that we don't duplicate text values (names of programs, names of lenders, names of categories, etc.); instead we use relationships to manage the data in one table for use in all other places.

Always enforce Referential Integrity. Otherwise those lines in the diagram are nothing more than a suggestion, not a rule.

Note that the LenderContacts table is not properly Normalized. You have three repeated fields "TaniaAccess", "MikeAccess" and "AlexAccess". Here's a good article on resolving Repeated Columns.