Show records with unique field

[RCurtin]

Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.

What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.

I've attached a screenshot and here is the sql:

SELECT  tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals.*
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"

I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.

Thank you,
RCurtin

 

[RCurtin]

I figured it out. This tutorial/sample was very useful:

http://www.techonthenet.com/access/queries/max_date.php