Showing ""Current "" status on a record

I have a couple of tables and a linking table that I am working with and trying to utilize to pull data for a field back to one of the parent tables. "Accounts" (table) may have one or more "Supp...