I am looking to retrieve a list of unique People IDs that have been visited by account managers.
I have three tables: Donors, Account Managers, and Contacts. The fields are like:
Manager First Name,
Manager Last Name (edited)
I'd like to be able to find out how many Visits (value in Contact Type) were made by an Account Manager to UNIQUE Donors. So if we have managers that are reporting a high quantity of visits, but they're all to the same 3 people, we want to tell them to branch out. I'm guessing this will use some type of Set Analysis or Aggr(), but I'm open really to any solutions.
I can't improve on Sunny's solution but can I make a suggestion about your data model? There's a synthetic table that I think highlights a little flaw in the logic. If the Manager ID field really belongs in the Donors table then, for the purposes of this exercise, it's not needed in the Contacts table too .
Maybe the Contacts table is the more natural home for Manager ID. If you remove this field from Donors we still get the correct result.
I think the Manager ID field is being overworked. It's not fair to ask it to tell us who the current manager is for a donor and also tell us who made each contact because over time contacts to a donor could be made by different managers. It might be that your database has more than one id field to meet these different requirements.
Totally agree about the model. I'm open to reworking it, but wasn't sure if there's an obvious way around it; I'm still learning about creating Link/Key tables in order to get everything to fit nicely from time to time. This is a solution I'll be working on though...
To your suggestion, the Manager ID belongs in it's own table. In theory, an Account Manager SHOULDN'T contact another Manager's Donors, but it happens from time to time by necessity. Do you have any suggestion on how to create a model where it works to get both assigned donors and contacted donors?
As far as the requirements go, it is necessary to be able to both tell who the ASSIGNED Manager is, as well as finding out who made the contact. One of the metrics we're trying to track is, basically, an overall view at ALL Contact Activity AND Contact Activity to only assignments. It's a pretty involved study, one I don't necessarily see all the value in, but it's a requirement nonetheless (I just do what I'm told!).