Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    3

    Unanswered: How to return 2 most recent dates

    Hello I have a question I hope someone can help me with.

    The Data Looks like the following:

    Shares Amount Date RIC
    12/29/2006 COMS.OQ
    03/30/2007 COMS.OQ
    07/27/2007 COMS.OQ
    08/31/2007 COMS.OQ
    12/28/2007 COMS.OQ
    03/28/2008 COMS.OQ
    07/18/2008 COMS.OQ
    09/26/2008 COMS.OQ
    01/31/2007 MMM.N
    03/31/2007 MMM.N
    06/30/2007 MMM.N
    09/30/2007 MMM.N
    01/31/2008 MMM.N
    06/30/2008 MMM.N
    02/20/2007 SRCE.OQ
    04/23/2007 SRCE.OQ
    07/19/2007 SRCE.OQ
    10/25/2007 SRCE.OQ
    02/20/2008 SRCE.OQ
    04/18/2008 SRCE.OQ
    07/21/2008 SRCE.OQ
    10/22/2008 SRCE.OQ

    What I would like is for Each RIC to have a query that brings back the most recent dates in separate columns. For instance the output would be

    RIC Most Recent 2nd Most Recent
    SRCE.OQ 10/22/2008 7/21/2008
    MMM.n 06/30/2008 01/31/2008
    COMS.OQ 09/26/200 07/18/2008


    I was think DMAX may work but I can not get it to work. If anyone has any ideas I would be very appreciative. Thanks in advance.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What if the top two dates are the same? What would you want back?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2008
    Posts
    3
    It won't be possible because the Date and RIC are both primary keys so I can't have redundant dates given a RIC. So the dates should brought back should be different.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I think I get it now. You want it grouped by RIC first, THEN pull back the first and second most recent.

    Correct?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Nov 2008
    Posts
    3
    That is exactly what I want it to do. Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •