Results 1 to 4 of 4
  1. #1
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37

    Return Field in Query based on Max Value of Other Field in another Table

    I have two tables BirdInfo (which lists all the bird information, i.e. Band, Sex, Age, etc.) & BirdMovements (which logs all the aviaries the bird has been during their time in our research lab and the date they moved to a new aviary i.e. band, aviary, date they moved there).

    In a query, I want to list all the BirdInfo, as well as list the last location they were housed. I feel like the expression needs to be something like:

    LastLoc: DLookup("[NewAviary]","Tbl_BirdMovements",... And this is where I get lost, but in essence I want it to find each birds latest date in the Bird Movements table.,


    In excel, I wrote something along the lines of what I put below and it worked.

    =Index(BirdMovements[NewAviary], match( if( BirdInfo[Band] & Max(if(BirdInfo[Band]=BirdMovements[Band],BirdMovements[DateMoved])), BirdMovements[Band] & BirdMovements[DateMoved],0))



    BirdInfo

    FNR February 5, 2017 COOP
    FNP January 1, 2017 DARWIN
    FWH January 1, 2017 MILE
    FYGB July 7, 2017 ISLAND

    BirdMovements
    FNR 01-Jan-15 CUBE
    FNR 02-Jan-16 CUBE
    FNR 05-Feb-17 COOP
    FNP 01-Jan-16 CUBE
    FNP 01-Jan-17 DARWIN
    FWH 01-Jan-16 CUBE
    FWH 01-Jan-17 MILE
    FYGB 07-Jul-17 ISLAND

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Possibly a TOP N nested query will accomplish, review http://allenbrowne.com/subquery-01.html#TopN

    Or possibly:
    SELECT * FROM BirdMovements WHERE DateMoved = DMax("DateMoved", "BirdMovements", "Band='" & [Band] & "'");

    Or possibly:
    SELECT Band, Max(DateMoved) AS LastMovedDate FROM BirdMovements GROUP BY Band;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37
    Thanks June7 however, this does not seem to work correctly, but it is really close...

    So far SQL code is as follows:

    SELECT BirdMove.Band, BirdMove.NewAviary, BirdMove.Move_ID
    FROM BirdMove
    WHERE BirdMove.Move_ID IN
    (SELECT TOP 1 Move_ID
    FROM BirdMove AS Dupe
    WHERE Dupe.Band = BirdMove.Band
    ORDER BY Dupe.NewAviary DESC, Dupe.Move_ID DESC)
    ORDER BY BirdMove.Band, BirdMove.NewAviary, BirdMove.Move_ID;

    I also keep an AutoNumber Index of every move, I imagine very similar to your OrderID example, so I used the Move_ID (but I tried using DateMoved and that did not work either). Unfortunately, this did not seem to work. It listed the two females I have in my database right now as sample data but for both females it returned the NewAviary of the second female and it returned the oldest date for the female with the wrong location. I tried removing the ORDER BY, and that made it select the 2nd result for F2P instead of the first.

    This is the query I created of the BirdMovement Table:


    Move_ID Band NewAviary DateMoved
    M-0001 F2P DARWIN 01-Sep-2016
    M-0002 F2P CubeNorth 09-May-2017
    M-0003 F2P COOP 13-May-2017
    M-0004 FNR COOP 01-Sep-2016
    M-0005 FNR CubeWest 11-May-2017
    M-0006 FNR DARWIN 14-May-2017

    This was the result I got with the query I wrote above:

    Band NewAviary Move_ID
    F2P DARWIN M-0001
    FNR DARWIN M-0006

    When it should return for F2P...

    F2P COOP M-0003

  4. #4
    CodenameAter is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jun 2015
    Posts
    37
    Okay, so like I said, I am a complete novice... After editing my post three times, I realized my own mistake lol...

    If anyone is curious, the solution was making the list order by Dupe.Move_ID only as this was the only field I wanted to use to order for each band.


    SELECT BirdMove.Band, BirdMove.NewAviary, BirdMove.Move_ID
    FROM BirdMove
    WHERE BirdMove.Move_ID IN
    (SELECT TOP 1 Move_ID
    FROM BirdMove AS Dupe
    WHERE Dupe.Band = BirdMove.Band
    ORDER BY Dupe.Move_ID DESC)
    ORDER BY BirdMove.Band, BirdMove.NewAviary, BirdMove.Move_ID;

    Thanks again June!

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

Similar Threads

  1. Replies: 3
    Last Post: 05-31-2014, 11:37 AM
  2. Replies: 1
    Last Post: 08-31-2011, 04:03 PM
  3. Replies: 3
    Last Post: 08-26-2011, 12:11 PM
  4. Return field value based on the same table
    By snoopy2003 in forum Queries
    Replies: 2
    Last Post: 03-05-2011, 02:45 AM
  5. Replies: 1
    Last Post: 03-01-2011, 04:03 PM

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