Results 1 to 2 of 2
  1. #1
    DannyBoy is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    4

    Question Retrieve only latest version from a single table

    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!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Try TOP n parameter. Review: http://allenbrowne.com/subquery-01.html#TopN
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-30-2012, 07:00 AM
  2. Replies: 5
    Last Post: 01-18-2012, 12:46 PM
  3. How to fetch latest records from table
    By India62 in forum Access
    Replies: 13
    Last Post: 05-19-2011, 11:43 PM
  4. Replies: 1
    Last Post: 05-17-2011, 05:19 AM
  5. How to retrieve field names in a table
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 12-05-2010, 09:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums