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!
Bookmarks