Trying to average last 3 records only (top 3)

So I'm trying to create a query that will produce an average (totals) column based on the TOP 3 records associated with two other key field values. Basically an average of the three most recent events. I can easily create the query to do the OVERALL averages, but whenever I try to use the built-in TOP N tool it only returns the top 3 accounts or locations, not an average of the TOP 3 results for each account and location.

The bold statement in the code is what I'm trying to only pull the 3 most recent matching records to produce the average for, this has been driving me nuts for a few days trying to solve it, and it seems like it should be simple.

There's a date field in dbo_CompletedAccountDrives and I can even limit it to only results from "X" time period, but that doesn't help because my end users want last 3, regardless of time elapsed.

Re: Trying to average last 3 records only (top 3)

I tried that a few ways but couldn't seem to get it to work. Whenever I created the sub-query they would only return the top 3 records in total, so when I try linking the key fields all but 3 records came back null. Not sure what I was doing wrong there.

Re: Trying to average last 3 records only (top 3)

Normally this is no big deal if values are spread out and in any case users don't usually know what they want in that case anyway. Just be prepared in case of questions. But if there are a lot of ties in your data then the results to be skewed towards the most commonly occurring values if such a tie is encountered.

Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

You are rich in proportion to the number of things you can let alone.
-- Henry David Thoreau

Re: Trying to average last 3 records only (top 3)

HAH, that's actually how i have it working now. It's just averaging a recent time span and giving them a number, which is a pretty good estimate. this database drives a piece of CRM front-end software that has a built-in 3 drive average calculation, so the numbers from my data never matches what they're seeing in the system and then people get suspicious about the reliability of the data, then I either have to shrug it off or explain until they fall asleep why it's such a P.I.T.A to provide.

There's even a stored procedure on the server that's supposed to be able to return that 3DA but I've never been able to successfully invoke it. I've tried pass throughs, recreating it in VBA, all sorts of stuff.

Re: Trying to average last 3 records only (top 3)

You might want to provide some sample data that illustrates the problem (and it may be that identifying such sample data would be well on the way to a solution already).

There's even a stored procedure on the server that's supposed to be able to return that 3DA but I've never been able to successfully invoke it. I've tried pass throughs, recreating it in VBA, all sorts of stuff.

Do you have the SQL (?) for this stored procedure? Do you have a connection to that server (if so, what have you tried in order to call it?)

Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

You are rich in proportion to the number of things you can let alone.
-- Henry David Thoreau

Re: Trying to average last 3 records only (top 3)

I was able to pull the code for the SP and tried re-implementing it in Access, both via an access query and running a Pass-Through but neither would work, had all sorts of execution and/or connection issues even though I can connect directly to the server fine otherwise.

I'll add that the code in the above procedure doesn't actually do exactly what I want/need it to and it's poor design on the part of the creator because there's a sub-template to "AccountID" that I want to also include as a layer for the average. So it should be TOP 3 records in DriveProductCollection by AccountID and LocationID (locationID being the sub template of account).

Re: Trying to average last 3 records only (top 3)

Okay, well in this case the stored procedure is useless since it doesn't do what you want anyway. I was under the mistaken impression that getting that result was the goal. So I think you can go back to post number 4 above as the next step.

Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

You are rich in proportion to the number of things you can let alone.
-- Henry David Thoreau