I started a post on your issue a couple of times, but had to move on before completing it.
Objective said:
need a table that allows me to collect employees sales figures in order to work out commision
Sound simple, but there are several issues. For example, how are you going to know the total sales. This affects the "how".
There are two ways I can see you will know the total sales...
1) You have either an Order / SalesOrder system, or an Invoicing system in place.
2) You plan on just entering total sales as supplied or calculated. (Other data will feed your subsystem)
If you are going to use the first option, you need to ensure that the sales person info is capture for the sales.
Assuming...
tblSalesPerson
SalesPersonID - primary key
LastName
FirstName
...etc capture other things unique for the sale person
tblCustomer
CustomerCode - primary key
CustomerName
...etc capture other things unique for the customer table
tblItem
ItemCode - primary key
ItemName
Active - boolean, yes or no item is still sold
DefaultItemPrice
Okay, now I am going to use an ordering system based on the above. An invoicing system would use a similar approach.
tblOrder
OrderID - primary key
CustomerCode - foreign key to tblCustomer
SalesPersonID - foreign key to tblSalesPerson
SalesDate
CustomerReference
DeliverToAddress
...etc, things unique to the order
tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to tblOrder
ItemCode - foreign key to tblItem
ItemQuantity
ItemPrice - currency
The important thing in the above is that the SalesPersonID identifies the Sales person who will get credit for the order.
Total sales would be Sum(ItemQuantity * ItemPrice) for a given month using the OrderDate.
...If you are not going to record individual sales, which means you will be feeding this sub-system with sales numbers from another system.
tblSales
SalesID - primary key
SalesPersonID - foreign key to tblSalesPerson
EndSalesDate - date
PeriodSales - currency
The above just captures the sales for the month as provided by another system.
AnotherObjective said:
...employee target (sales) ...
Okay, the sales force will have sales objectives. Let's revisit the the
tblSales table.
If you are tracking invidual sales, you just need...
tblSales
SalesID - primary key
SalesPersonID - foreign key to tblSalesPerson
EndSalesDate - date
TargetSales - currency
If you are just entering total sales...
tblSales
SalesID - primary key
SalesPersonID - foreign key to tblSalesPerson
EndSalesDate - date
TargetSales - currency
PeriodSales - currency
...Moving on
Now that you have the total sales for the month, either provided or calculated, the commission can be calculated.
Commissions can be calculated by a flat fee, or by individual product. For example, bonuses or other insentives to promote the sales of one product over another.
Regardless of appraoch, I suspect you need to store the commission rates since they may change over time, and you are dealing with money.
Let's work on the simple approach first, a flat commission.
(I am going to ignore the other possibility where different sales people can have different sales commissions)
tblCommission
CommissionDate - date, primary key
CommissionRate - single percision
Discussion:
If and when the commission rate changes, a new rate is entered, and new date is entered. The rate used for calculations is the record with the newest date.
As part of "Normalization", some would state that since the commission is a calculated value. However, since we are dealing with money, I personally would prefer to store the value. This would indicate the amount paid to the sales person.
You can actually store this information on the
tblSales since it has the same dependent properties...
tblSales
SalesID - primary key
SalesPersonID - foreign key to tblSalesPerson
EndSalesDate - date
TargetSales - currency
PeriodSales - currency
CommissionPaid - currency
CommissionCalculated - currency
With the order entry system model, you would not have the field
PeriodSales in the above table.
...Calculating Commission
You have not provided much in the way of details on how sales calculations are done. However, I feel a function is the best way to approach the problem. A function provides a central area for the calculation instead of using code through out the various forms and reports.
Code:
Function CalcCommission(lngID as Long, SalesDate as Date) as Currency
Dim rst as DAO.Recordset
Dim strSQL as String, sngRate as Single, curSales as Currency
curSales = 0
sngRate = 0
'The SELECT statement for sales will differ depending on how
'you calculate / store total sales
strSQL = "SELECT PeriodSales FROM tblSales WHERE SalesPersonID = " _
& lngID & " AND Year(EndSalesDate) = " & Year(SalesDate) _
& " AND Month(EndSalesDate) = " & Month(SalesDate)
Set rst = CurrentDB.OpenRecordset(strSQL)
With rst
If .RecordCount Then
.MoveFirst
curSales = Nz(!PeriodSales)
End If
.Close
End With
If curSales <> 0 Then
'I guess one may have negative sales, so only test for 0
strSQL = "SELECT CommissionRate FROM tblCommission " _
& "Order By CommissionDate Desc"
Set rst = CurrentDB.OpenRecordset(strSQL)
With rst
If .RecordCount Then
.MoveFirst
sngRate = !CommissionRate
End If
.Close
End With
End If
CalcCommission = curSales * sngRate
Set rst = Nothing
End Function
The function just calcuates the sales * commission for the sales person for the given month. It does not update any tables.
To create / add the function, from the main menu, "Insert" -> "Module". After entering the code, you will be prompted to save the module. Use a meaningful name for the module.
need a form with comand buttons to allow a use to enter/edit records
- Use the form wizard to create your form based on tblSales. Call it frmSales.
- Open frmSales in design mode.
- Add a command button to the form. Cancel out of the wizard and do the following.
Make sure the properties window is open (from the menu, "View" -> "Properties")
- Select the newly created command button. Look at the "Other" tab in the Properties window. Change the Name to cmdCalcCommission
- Now select the "Events" tab in the Properties window. Select the OnClick event and the select the [Event Procedures] from the pick list.
- Click on the "..." button that appears to the far right of the OnClick field to open the VBA coding window.
Something liek the following should work...
Assumptions - field names used in the above form will be used.
Code:
If Nz(Me.SalePersonID, 0) Then
If IsDate (Me.EndSalesDate) Then
Me. CommissionCalculated = CalcCommission(Me.SalesPersonID, Me.EndSalesDate)
Else
Msgbox "Need to enter a date for End Sales"
End If
Else
Msgbox "Need to select a sales person"
End If
A lot of stuff in the above, and you seem to be fairly new to the Access "game". However, it is really not that tough. Access has a lot of great tools to aid you in your task -- QueryBuilder, Forms Wizard
Also the following is excellent documentation to arm yourself with...
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)
Micro$oft's answer to design and relationships...
Where to find information about designing a database in Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database
And a really good article on queries
Harnessing the Power of Updatable Queries
Richard
