Query a unique record based off of 2 criteria

[cowboy]

I have records that have tracking numbers and multiple records can have the same tracking number. I am trying to query to only have 1 of each record shown. I have a sub query that is a totals query and is set to group by the tracking number and provide the Max of a due date as shown below.




Then I have a query to inner join on the tracking number and max of due date to choose only those records from the filter query above, but add in all the other fields from the parent project as shown below.



And that works pretty well until there are multiple records with the same due date, then multiple records get queried as show below.



How do I filter by a second criteria to make sure I am only returning one record for each? I think my second criteria will be the larger of the sID, which is the auto number key for the submissions.
(One thing that I thought of while previewing this post is to run another total query on the results above and group by pID and then Max of sID and then run another inner join query to pull back in the extra fields from the project.)

Any help would be appreciated.

Here is a simplified version of the database if that is helpful:
unique records.zip

[June7]

Go ahead and try that.

Also, review http://allenbrowne.com/subquery-01.html#TopN

[Micron]

I have a sub query

What you show is a stacked query (where a query uses another query as if it were a table). A subquery is something else as you may note from the posted link.

[cowboy]

Using that link on the Subqueries, I think I was able to fumble together what I want and with just the 1 query with subquery and no the stacked queries. Thank you for the help. This is what I ended up with:

SELECT q_AllSub.*FROM q_AllSub
WHERE q_AllSub.sID IN


(SELECT TOP 1 sID
FROM q_AllSub AS Dupe
WHERE Dupe.pID = q_AllSub.pID
ORDER BY Dupe.Due DESC, Dupe.sID DESC)


ORDER BY q_AllSub.Due;

[Micron]

Glad you were able to solve it. Good luck with your db.