Forum Discussion
Access Lookup - pull thru of data from associated/linked table - data from two fields
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.
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.