Quarterly Sales Commission Database

[milkman2500]

I'm trying to create a database that can store client, employee and sales data. I also need to have the database calculate the quarterly commissions the sales employees have earned.

However, the commissions are deferred over future quarters and I need the database to calculate future quarterly commission payments at the time of sale. The quarterly commission statements will reflect current actual and future estimated payments.

I have basic tables for client and employee information. The quarter and sale tables are where I am struggling. I only want to input the date the sale occurred, and I want the database to automatically calculate commissions and determine the quarters that require payments.

I'm comfortable with calculating dates, however I can't create relationships with calculated fields. Can someone please recommend an alternative?

Here are the tables:

tblClients:

[ClientID]
[ClientName]
[EmployeeID]
​

tblEmployees:

[EmployeeID]
[EmployeeName]
​

tblSales:

[SalesID]
[ClientID]
[EmployeeID]
[SalesDate]
[SaleAmount]
[Commission]
[FirstQuarter] - is calculated from [SalesDate] and linked to QuarterID
[SecondQuarter] - QuarterID + 1
[ThirdQuarter] - Quarter ID + 2
[FourthQuarter] - Quarter ID + 3
​

tblQuarters:

[QuarterID]
[QuarterStartDate]
[QuarterEndDate]
​

Thanks.

 

[Isskint]

The first thing i should point out is that you should normally avoid storing calculated fields in your tables. By their very nature calculated fields are 'calculated' using a formula and 1 or more variables could be subject to change. Calculate these on the fly when you need them for forms and reports.

Ok, i would suggest creating a parameter query based on tblSales. This will require the sales quarter to be passed each time it is run and can either be done by the user through an inputbox (not recomended) or via a form where the quarter can be selected or entered. This query will then produce a list of any sales in that quarter. Based on this you create another query that groups by EmployeeID and sums SaleAmount (or if you are storing the calculated amounts SUM on these).