Trying to average last 3 records only (top 3)
Results 1 to 9 of 9

Thread: Trying to average last 3 records only (top 3)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    Here's the code I've got:
    Code:
    SELECT dbo_CompletedAccountDrives.accountid, dbo_rpt_DriveMaster.LocationID,  Round(Avg([dbo_DriveProjectionAndCollectedTotals]![ProductsCollected]),0) AS Products
    FROM (dbo_rpt_DriveMaster INNER JOIN dbo_DriveProjectionAndCollectedTotals ON dbo_rpt_DriveMaster.DriveID = dbo_DriveProjectionAndCollectedTotals.DriveID) INNER JOIN dbo_CompletedAccountDrives ON dbo_rpt_DriveMaster.AccountID = dbo_CompletedAccountDrives.accountid
    GROUP BY dbo_CompletedAccountDrives.accountid, dbo_rpt_DriveMaster.LocationID
    ORDER BY dbo_CompletedAccountDrives.accountid, dbo_rpt_DriveMaster.LocationID;
    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.

    Any help is greatly appreciated.

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    627
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Try using a sub query to get the top 3 records, then avg those in the main query.?
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  4. #4
    Board Regular
    Join Date
    May 2013
    Posts
    627
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Last edited by welshgasman; Jul 20th, 2019 at 04:35 AM. Reason: Added second link
    Office 2007
    Access novice. Sometimes trying to give something back

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,441
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    Just to put a wrench in it, Top N will not always return N values because of ties.
    https://support.office.com/en-gb/art...1-756fe298ce57

    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

  6. #6
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

  7. #7
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,441
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default 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

  8. #8
    New Member
    Join Date
    Apr 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    The SP code is:

    Code:
    USE xDbase
    GO
    SET ANSI_NULLS, QUOTED_IDENTIFIER ON
    GO
    CREATE FUNCTION [dbo].[AccountsLast3DrivesAverageProductsCollected](@AccountID int)
    RETURNS numeric(8,2)
    AS
    BEGIN
     declare @Ret numeric(8,2)
     select @Ret = avg(dbo.DriveProductCollection(driveid))
     from rpt_drivemaster
     where driveid in
        (select top 3 driveid from completedaccountdrives where accountid = @AccountID order by fromdatetime desc)
     
     if @Ret is null
      set @Ret = 0
     return @Ret
    END
    GO
    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).
    Last edited by Dbc23; Jul 23rd, 2019 at 09:54 AM.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,441
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •