Forum Discussion
Access Lookup - pull thru of data from associated/linked table - data from two fields
I have three tables: List 1, List 2 and List 3
Table - List 1 Fields:
L1Name
L1Name ID (unique Key Field)
Table - List 2 Fields:
L2 Program Name
L2 Program Name ID (unique Key Field)
Select L1 Name via LOOKUP associated with List 1 Fields - L1Name
Note: This sets up a one-to-many relationship of Programs for each Name in List 1.
For instance - Name is Tom. Tom has three programs - (1) Biking, (2) Running, and (3) Swimming.
Table - List 3 Fields:
L3 Person Registering for Program - Name
L3 Person Registering for Program Phone Number
L3 Program Person Registering For (Pull thru from LOOKUP)
L3 Name of Company Offering the Program (Pull thru from LOOKUP)
L3 Registration ID (unique Key Field)
Note: When a person created a registration for a program, they enter their name and phone number and then can LOOKUP and select a program thru a LOOKUP to Table 2 where they sort thru programs by company offering the program. Upon selection, the LOOKUP pulls thru both the name of the company and the program name as associated with the company.
I CANNOT GET THE PULL THRU TO WORK. THE LOOKUP in Table List 2 work with no problem but the LOOKUP and PULL TRHU in Table List 3 returns the number L2 Program Name ID (not the name) and no program name.
5 Replies
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.
- Still struggling thru this... being a never-use ACCESS user before - hoping that you can suggest a video or something showing how to connect two fields in one table that pulls information from another table. I tried setting up the direct relationships, but I am not successful plus it seems that I would not be able to do the intended look up for selection of the name and corresponding program per the above field lists.
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.
Thank you... great advice; having learned my database experience under the tutilage of the old 'DataPerfect' solution, happy to get the ins and outs of Access... I remember in the 1990s Access was notorious for trying to implement Dataperfect-like solutions only to experience similarities as what I see now. Good to know that things have not changed much in 30 years. LOL
One a serious note, Thank you and I will deploy as you instruct. Thank you!
