+ Reply to Thread
Results 1 to 2 of 2

Access Query - Top 5 Dates Returned

  1. #1
    Forum Contributor
    Join Date
    11-01-2012
    Location
    NJ
    MS-Off Ver
    Excel 365
    Posts
    109

    Access Query - Top 5 Dates Returned

    I have a column in an access query that is the Date. The query returns the top 5 most recents dates. What I would like to do is add another column to the query where the most recent Date would be 0, the second most recent Date, 1, third most recent date , 2 and so forth so that the query would return something like

    Date ] Date Lookback
    11/30/2014 ] 0
    10/28/2014 ] 1
    10/27/2014 ] 2
    10/15/2014 ] 3
    10/11/2014 ] 4

    I was trying something along the lines of

    Date Lookback: IIf([Date]=[Max],0,IIf([Date]=[Max]-1,1,IIf([Date]=[Max]-2,2,IIf([Date]=[Max]-3,3,IIf([Date]=[Max]-4,4)))))

    But this does not work as I am not sure how to use the max function correclty even after my web searches. If anyone could please help, would be appreciated!

  2. #2
    Registered User
    Join Date
    12-15-2011
    Location
    Washington
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Access Query - Top 5 Dates Returned

    Partition the table by the date. With SQL you would do a Over (Partition by <datecolumn>)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 05-14-2013, 04:39 PM
  2. Replies: 9
    Last Post: 07-31-2012, 05:54 PM
  3. [SOLVED] This query returned no data
    By Tjeerd in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-22-2005, 05:05 PM
  4. [SOLVED] No of Records returned by a Query
    By Soniya in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2005, 10:06 AM
  5. The Web query returned no data
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-09-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1