So, to get the most current VendPart takes a subquery and a trick. This is baptism by fire my friend.
Create a new innersubquery and drag VendPart to the screen. Create a criteria to eliminate any future EffectiveDates.
In your Display Fields, add Company, PartNum, EffectiveDate, and VendorNum.
Create a new calculated field called RowNum:
What this does is assigns the ROW_NUMBER to each record within the part in descending order (most recent to oldest). I did this is in a new query for demonstration purposes, and this only has the VendPart subquery in it. Notice what we now get:
We still have all the duplicates but now the most recent record is marked as 1 and each older record counts up. Armed with this subquery, we can filter everything else out except the 1s.
In the main query, select out only the 1s.
Now when we run this, we get:
…only one record per part - the most recent VendPart record per part. Use this subquery in place of the VendPart table in your query, and things will look a whole lot better.
The moral of the story is to pick easier BAQs to start with! 