I am working on a database that has one table let's call Assets, and a separate related table called Tracking. If an asset is moved around (and "tracked") several times, that asset will have several related records detailing each transfer. I'm trying to create a report showing all assets in one location, but when I do a search by the related Tracking::Location field, it returns all assets that were at that location at any time. I want it to only pull up the assets that are there now.
How can I get an Assets::LastLocation field to match the Tracking::Location field with the most recent date? I can pull the max date of the last time it was transferred, but not the corresponding location.