Salesman's points query

[thenoisydrum]

Hi there,
This is a real situation that I need a bit of help with please.
Where I work the salesman that sells the most of a particular brand of tyre gets 3000 points, the second highest gets 2000 points and the third gets 1000 points.

I have a few very basic queries that calculates the number of tyres sold month to date and that then calculates the projection for the whole month. I sort this final query into descending order by projected units and then in the properties limit it to the top 3 values. (If there were more than one person in joint 2nd place they would both get 2000 points so this method is fine).

I want to add a column on the end that "applies" the points earned - How can I do that?
I have tried reading about the rank query but I don't think that is the correct way to go about it, or is it?

Can anyone help please?

Thanks in advance

'drum

 

[plog]

Can you post sample data from your table and then what data should result from that starting data? Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 145, 4/23/2009
Larry, 59, 5/6/2007
Sally, 42, 8/8/2009

 

[thenoisydrum]

I attach the query as a text file. The points would be added as column on the end giving 3000, 2000 and 1000 points.

Thanks

'drum

 

[plog]

This query will produce exactly the results you want for the data you provided me:

SELECT *, 1000*(DCount("[Projected]", "YourQueryNameHere", "[Projected]<" & [Projected])+1) AS Points
FROM YourQueryNameHere

You didn't provide a name for that query, so I used 'YourQueryNameHere'. Replace it with the name of the actual query. Again, this will produce the results that you want for the sample data you provided. When it fails for the actual data, post back better sample data.

 

[thenoisydrum]

Ok plog, here is the actual code for the query (AVFinal);

SELECT TOP 3 AVBase1.Month, AVBase1.Criteria, AVBase1.[Fitter Name], AVBase1.MTD, Round((AVBase1!MTD/[Current Month Position]![nth Trading Day])*Month![Trading Days],0) AS Projected
FROM (AVBase1 INNER JOIN [Current Month Position] ON AVBase1.Month = [Current Month Position].Month1) INNER JOIN [Month] ON AVBase1.Month = Month.Month1
ORDER BY Round((AVBase1!MTD/[Current Month Position]![nth Trading Day])*Month![Trading Days],0) DESC;