Originally Posted by
Bill Walsh
I average the last 10 scores of each player and that becomes his or hers new quota. I also use last 20 scores to decide whther or not they are improving or not and reflect that on their scorecard.
You create an User Defined Function (UDF) with table name, table field, aggregate function code, number of rows, date field name, player ID field name, and player ID as parameters (In case you don't plan to use the function elsewhere, you can hardcode some of them into function instead).
You can use the function then in form control, query or in VBA code to calculate the aggregate value of any given number of last records for player, e.g.:
Code:
SELECT a.PlayerD, YourUDF("YourTable","Score","AVG","DateField", 10,"PlayerID",3) AS AggregateValue FROM (SELCT DISTINCT PlayerID FROM YourTable) AS a
UDF uses a query constructed with passed parameters taken into account - like:
Code:
SELECT b2.PlayerID, (SELET AVG(b1.Score) FROM (SELECT TOP NumberOfRows b0.Score FROM YourTable 0 WHERE b0.PlayerID = b2.PlayeID ORDER BY b0.DateField DESC) As b1) FROM (SELCT DISTINCT PlayerID FROM YourTable) AS b2