How to relate two tables with the key to a third table?

Question

In Referral (key Record ID), there is a field called Referring Contact - which is a key for the table Contact.

In Activity (key Record ID), there is a field called Activity Contact - which is a key for the table Contact as well. It also has a field called Team Member ID - which is a key for the table Team Member.

The users want to know, for every Referring Contact in table Referral, the name of the Team Member who had an activity most recently with that Referring Contact.

The Activity table contains this information but I cannot figure out how to get it into the Referral table to report - I think the two tables should be related, then there should be a formula field that brings the (newest) Activity record, with its associated Team Member, into the Activity Record. But how to relate the two tables, since the "matching" Contact fields are not the key of either table?

DonLarson · Accepted Answer

You create a Summary field in the Contact table from the Activity table to get the Maximum Record ID of the Activity related to the Contact.

The next thing is you make relationship from Activity to the Contacts table (the red one) and use that Summary field as the Reference. Do NOT let it create a new field.

Then you make Member Name from Activity a look up field in Contacts through that Relationship.

Now every Contact will show the Member Name for the very last Activity created.

DonLarson · Answer

Charlotte,I would build this

Sign up

Login to the community