I know this is very simple but I can't seem to figure it out today.

A system that I'm working on has an orders table and a commissions table. Commisions are related to orders by an order ID and a single order can have multiple commissions attached. Each commisions record also shows a SalesRepID.

From the orders table I need to show the SalesRepID from the Sales rep that has the highest commission on that order.

Any suggestions would be apprecaited.

If you only want to display only the one record with the highest commission and name, then when defining the relationship between the orders and commission table, you can have the commission table be sorted Descending based off the commission number. Then assuming that you have a relationship from your commission table and the salesperson table, you can just just put the two fields on your layout.

Commission::CommissionAmt

Salesperson::Name

However, are there cases where the sales commission can be the same for more than one person? What about then? You may just be better off using a portal to display the info.