Just to clarify... Are the fields that you are doing the lookups on in the Awards table?
If so have you tried only using the pk --->fk fields.. does this work?
Also could you post your lookup calc so we can help..
Thanks for the reply. I realized that my problem is simple- the lookup is not by calc, but by field option. Thus, it's going to the first matching record, which does not necessarily have anything in the specified field. For example, I have a record in Roles 2 with data in the"PI_Email" field, but because there are 2 other records in the table that match based on the relationship criteria, the data isn't being pulled to the appropriate record in Awards as it's not the "first matching record".
That being said, I have not experimented with doing lookups by calculation. Any tips on how to started?
You'll encounter the same issue whether you use the "looked up value" or "calculation" auto-enter options. It would appear that you need a different relationship that matches to the correct record.
We might be able to suggest something if you describe your data and how you need this to work in more detail. The key detail being, what data in the looked up data identifies the specific record as the correct one from which to look up this information?
Basically I'm trying to lookup (for insertion into a single record in Awards) three separate instances from the Roles 2 table without using any more table occurrences. It's probably a bad idea. A portal won't work, I need all the email addresses to be part of the same record.
I'm just going to add more occurrences of Roles 2. Thanks anyway!