Retrieve only latest version from a single table

[DannyBoy]

Hi,

I have a table currently containing the following data;

• Record ID
• Recipient
• Recipient Location
• Transmittal Date
• Transmittal Number
• Document ID
• Document Title
• Document Rev

There are others, but they aren’t relevant to this query.

Over time, records are updated and re-issued to different people for various reasons. I need to query my table to retrieve a list of the latest version of each document released to each recipient. I am comfortable generating a query to list all documents issued to each location, but what I can’t get my head around is how I can update the query to only retrieve the latest version.


Preference would be to utilise the Document Rev field, however this contains a mix of alpha-numeric values – they run A to Z then 0 to infinity, so rev C would be later than rev A, but rev 1 would be later than C etc. The other field potentially to utilise would be the transmittal data, as it could be assumed the latest issued transmittal would include the latest version.

This is my current query;

SELECT AllIssuedDocs.TO_RECIP, AllIssuedDocs.RECIP_LOC, AllIssuedDocs.XMIT_DATE, AllIssuedDocs.XMIT_NO, AllIssuedDocs.DOC_IDEN, AllIssuedDocs.DOC_TITLE, AllIssuedDocs.DOC_REV
FROM AllIssuedDocs
WHERE (((AllIssuedDocs.TO_RECIP)="<name of recipient>"))
ORDER BY AllIssuedDocs.DOC_IDEN;

Anyone able to help me out please?

TIA!

[June7]

Try TOP n parameter. Review: http://allenbrowne.com/subquery-01.html#TopN