Results 1 to 5 of 5
  1. #1
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11

    Finding 3 most recent (past) related records

    I have a chronlogical table of blood drives with associated data fields.



    What I need to be able to do is calculate the average # of blood donors at the most recent 3 previous blood drives to the one scheduled (upcoming).

    Each drive has a unique ID
    Each account (the field I need to find the 3 most recent prior drives associated to) has a unique ID

    I know about FIRST/LAST and MIN/MAX but those don't seem to work for what I'm trying to do.

    I need something like (pseudo code): For currentdriverecord by Account# Find Next Drive with Date <= currentdriverecordDate (iterate X3) then return the total units collected values for each of those 3 drives (along with their DriveID and Date).

    It'd be fine if I have to chain this, so do it once to pull the most recent prior drive, then run again to find the one before that, etc...

    I'm not sure if this can be done without writing a custom function, but even if that's the case I'm lost for where to begin in creating that function.

    Any ideas?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    select TOP 3, sort Date Descending

  3. #3
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11
    That DOES seem to be what I need but I'm continually pooching the syntax because obviously I'm not very SQL fluent (I'm using the QBE almost exclusively) but I'm learning, hence why i'm here

    This is the query that pulls the core data I need for the UPCOMING drives that I need to link back to the historic drives:

    Code:
    SELECT DriveInfoSource.DriveID, DriveDates.WeekdayName, CInt([dbo_Accounts]![AccountID]) AS AcctID, DriveInfoSource.MobileOrCenter, DriveInfoSource.State, DriveInfoSource.DriveDate, DriveInfoSource.DriveName, DriveInfoSource.DriveStatus, DriveStaffing.SumOfStaffRequested AS StaffRequested, UnitsCollected.ProductsProjected, UnitsCollected.RBCProjected, DriveInfoSource.Center INTO KatieDrives
    FROM (((dbo_Accounts RIGHT JOIN (DriveInfoSource LEFT JOIN dbo_DriveMaster ON DriveInfoSource.DriveID = dbo_DriveMaster.DriveID) ON dbo_Accounts.AccountID = dbo_DriveMaster.AccountID) LEFT JOIN DriveStaffing ON dbo_DriveMaster.DriveID = DriveStaffing.DriveID) LEFT JOIN UnitsCollected ON DriveInfoSource.DriveID = UnitsCollected.DriveID) LEFT JOIN DriveDates ON DriveInfoSource.DriveID = DriveDates.DriveID
    WHERE (((DriveDates.WeekdayName)="Saturday") AND ((DriveInfoSource.MobileOrCenter)="Mobile") AND ((DriveInfoSource.State)="IL") AND ((DriveInfoSource.DriveDate)>=#1/1/2019#))
    ORDER BY DriveInfoSource.DriveDate;
    It SEEMS like something like this should kick back the next three prior DriveIDs, but I keep getting syntax errors OR a blank query returned:

    Code:
    SELECT TOP 3 dbo_DriveMaster.DriveID
    FROM KatieDrives LEFT JOIN dbo_DriveMaster ON KatieDrives.DriveID = dbo_DriveMaster.DriveID
    WHERE KatieDrives.DriveDate < DriveMaster.DriveDate;

  4. #4
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11
    Ok, already made progress, but can't get it to show me ALL of the links only the LITERAL top 3, while I need the top 3 for EACH accountID listed in "KatieDrives".

    Code:
    SELECT TOP 3 dbo_DriveMaster.DriveID
    FROM (KatieDrives LEFT JOIN dbo_DriveMaster ON KatieDrives.AcctID = dbo_DriveMaster.AccountID)
    WHERE (((KatieDrives.DriveDate)<(dbo_DriveMaster.FromDateTime)));

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think you need to create ranking query in which you would group by the account ID and get the IDs of the three most recent queries. Here are some links to show you how to do that:
    https://bytes.com/topic/access/insig...-ms-access-sql
    https://stackoverflow.com/questions/...rouped-ranking
    https://stackoverflow.com/questions/...-in-access-sql

    Once you have your three most recent drives by account ID you can create another query to do the summing of the samples by drive ID.

    If you get stuck maybe you can post a stripped down version of your db so we could have a look.
    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-22-2018, 07:47 AM
  2. Replies: 27
    Last Post: 03-11-2018, 01:32 PM
  3. Replies: 3
    Last Post: 03-22-2014, 04:32 PM
  4. Replies: 7
    Last Post: 07-02-2012, 10:50 PM
  5. Commiting changes in past records
    By spleewars in forum Programming
    Replies: 3
    Last Post: 04-17-2012, 08:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums