Calculations that summarizes relational data in portals

[LisetteG]

I have a database the documents grant awards, grantee information, host affiliation, etc. in their own respective relational tables. I have been doing most of this based on the knowledge I gained from my previous thread.

The layout that shows records from the Hosts table details all the Host's information and it includes a portal. The fields on this portal has a list of all the grantees who were affiliated with this host during their award period, (Grantee Name, Program Year, Academic Area). It's a great way to quickly identify past and present trends. I want to take it a bit further by attempting to do the following:

1. Create two calculations that would let the user know if the grantee has had other host affiliations in A.) the same grant year and/or B.) other grant periods. A and B represent the two different calculations.

Jon B received a grant in 1995 and researched at 2 universities, ABC University and DEF University. In the ABC University record in the Host Table I would like a field in the portal that let's the user know that in addition to ABC University Jon B also researched at DEF University. And vice versa for the DEF University record.

And another calculation that would briefly let the user know if the grantee in question has had other grants awarded and include information if has other host affiliations.

2. Create a calculation for when a grantee repeats the same award affiliation at another time (program year) so that the grantee name does not appear twice on the portal, and indicate it's multi-year affiliation under the program year field, by listing the years of affiliation. If Job B got awarded a serial grant, which means he repeated his grant details in 1996 and 1997. Instead of listing Job B three times in the portal in the ABC University record. I would like for his name to appear once and have 1995, 1996, 1997 show up under program year.

Please let me know if you need further clarification, I think I covered all the details. I really appreciate your attention.