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!