Comparing the Results of a SQL statement

[simeybt]

Hi All,

What I am trying to accomplish is run a Query that compares the current amount of sales that person has sold against the number of sales they should be selling i.e. their target, in a specific week. The query is working up to the point of pulling back the actual sales and the , but what I now need to do is to compare the Actual sales of a person against the Target (SalesMade/Target=result). Depending on the result I want to give that person a point/s and add it to a new table i.e. tblSalesScore. I’m not sure if this can be done via query(I don’t think it can). Any information on how to do this via queries or via VBA would be excellent.

 

[KenReay]

Hi

No reason why it cannot be done in an update query, cannot give exact example on the info so far, but in principle if you have a query which returns (at present):
SalesPersonId
ActualSales
TargetSales

just add a calculated column
Score:IIF(ActualSales>=TargetSales,1,0)

then join to your score atble and updatre cumulative value with Score

But.. sorry I just have to ask it, if you can derive the score from the existing table, why store it again and risk the chance of things getting out of step

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[simeybt]

ok here’s a little more info.
want i do it take the total_sales / target_sales = PercentageResult. Now if that person has exceeded there sales target by 10% they get 7 point if they exceed it by 5% they get 6 and so on until they get 0 points. The sales_query has a number of different sales targets for different products. SO there will be a collection of points for each different type of product for each seller. I want these to be summed up and dived by the possible sales points to give me an overall percentage. i.e Total_Sales_Points / Total_Possible_Sales_Points = Overall_Percentage. if this can be done without the extra table great, but I want to be able to access the result eventually via ASP so I thought this extra table would speed things up a bit when it gets to that stage.

 

[KenReay]

Hi

OK, this is getting a little heavy and I do not have time to spend doing the detail, but I would have a table of 'rules' eg Product, Percent, points, then I would write a user function which would look up the score in the above mentioned table based on the ActualSales/Target percentage, this function can be included in the query. This way, when (note if notice) they decide to change the scoring rules you do not have to change the SQL, just the 'rules' table

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[simeybt]

A few good suggestion and a few helpful pointers

Thanks for the Advice. the rules table sounds a excellent idea, a quick pointer on the user function would be excellent if can spare the time if not I’ll just post another topic on the site

Thanks Again

Simon

 

[KenReay]

Hi

The user function, what do you need to know?

Where to put it - in a module

Public Function BrowniePoints() as Integer
..etc
End Function

we would have to pass in the product, the actual sales and the target sales, so

Public Function BrowniePoints(ProductId as Long, Target As Currency, Actual As Currency) as Integer

do you need more?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[simeybt]

so if I’m right I just add this function to a module. this function will contain all the conditions for working out the score for that person. The points for each product will be imported from the 'rules' table via this function. let me know if I’m right in the assumption.

Simon

 

[KenReay]

Hi

Exactly right, it is a little black box

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[simeybt]

ok I’ve implemented the function an I pass in the Target and the Total sales. It works out the first score perfectly, but all the other scores come up as #error. when I step thought the code the values being passed into the function each time it is called are the values from the first record. here is my function

Function GenerateScore(dblProductTartget As Single, dblActualSales As Single) As Double

Dim dblProductScore As Double

If dblActualSales < 1 Then dblActualSales = 0

dblProductScore = dblActualSales / dblProductTartget

If dblProductScore >= 1.21 Then
    GenerateScore = 1
ElseIf dblProductScore >= 1.06 Then
    GenerateScore = 2
ElseIf dblProductScore >= 0.95 Then
    GenerateScore = 3
Else
    GenerateScore = 6
End If


End Function