This sounds like a classical Many to Many Relationship.
One Agent has Many Transaction,
but also
One Transaction may have Many Agents.
The solution is to have a join table in the middle called, for example, Transaction Agents.
That will then allow you to have an embedded report on the agent table for all the transaction agents they've been involved in. That would list the actual transaction details. And then on each transaction record it would show the list of agents from the transaction agents table. The commission rates could be calculated based on look up fields from the agent, if that is how it works and knowing that there could be multiple agents the system would know how many agents are involved on a particular transaction as they are probably splitting a commission and getting less commission if two people are sharing it.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
