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