[AccessD] Another Commission Dilemma

Dear List:

    We have another problem with the commission structure we are trying to
setup. The boss wants the current system where we enter customer and sales
info into the tblCustomers and tblOrders tables. The tblEmployees tracks
each employees sale. We also have a tblReturns for those orders that get
returned. The commission system we use now is in Excel and we would like to
integrate it into Access fully.
    I'm relatively new to database design and am trying to design a system
where each week the sales get totaled for each employee and they get setup
on a special commission structure where: 0-2 sales = no commission, 0-7 =
regular commission %, 8+ sales would get a different commission % as the
sales add up. This is because we have integrated a flat bonus amount ($50
for 8-10 sales, $100 for 10-12 sales, $150 for 10-15, etc, etc.) and we have
to change the commission % for each sale to accommodate this flat bonus
amount.
    However, there is also another variable. There is a special bonus if the
entire sales staff meets a goal that every salesperson who received 5+ sales
that week will receive. It goes $75 for 60-69, $100 for 70-79 and so forth.
    What I need to do is produce a system where it looks at both individual
sales and group sales to produce the total commission amount for each
salesperson on a weekly basis. After this I would need to integrate a
deduction system for order returns and random deductions for employee
tardiness, corrective action, etc.
    If anyone can help me with any part of the design for this project it
would be greatly appreciated.

Sincerely,

Adam Faulkner
Res-Com