I have a database that is created to calculate commissions for sales staff (Commission Table). I have group of accounts that were assigned to a previous rep and will now be disbursed among the existing sales people. I have created a related table (Special Accounts) to hold this list of accounts. (Customer ID) is the common field between the two tables.
My issue is I need to figure out a way gather the sum of sales in the Commission Table for those accounts where Customer ID matches the Special Accounts, but without having to do a specific query on the data - which would take too long for all of these accounts, with all of the reps that the accounts are reassigned to.
I'm looking for a way that when I populate the sales for which commissions will be calculated, that all sales for the related Customer ID will be summed in a field.