Lookup based on relationships

[Brulotte]

I'm sure this is common but for some reason I just can't get my arms around this one.

I'm creating a commissions tracking database for sales. There are three related databases: Sales Reps, Services and Main. The Sales Rep database holds a name and a group (group because not all reps are paid the same). The Services database has a Service name, Group A Commission, Group B Commission and Group C Commission. The Main database is where the customer information is entered, a Service is selected (Value List generated from Service Name in Service database), Sales Rep (Value List generated from Sales Reps database).

Here is where I run into the problem. How do I get the Main database to look at the Sales Rep table based on the rep selected from the drop down, find the group they belong to then look up the group in the Services database and pull out the commission amount?

I'm sure this is simple and has been done a million times. I just can't see the solution. Maybe because it's Saturday.

[Chuck]

If I understand you correctly, this is what you need to do. Make the Commossion_Amount field a calculation like this:

Case(