MS Access – Reference previous same field of previous record

imodett 61 Reputation points
2022-06-17T17:35:19.233+00:00

I have a table with Project and Date fields. I want to build a query that will tell me what the previous record’s Date field states of the equal Project if it were sorted by an ascending sort. For example, if I were at Project 6036’s date field equal to 8/19/21. The Previous date of the same project would be 3/10/21. What is the best way to achieve this?

Example Table:

Project, Title, Date
6036, 1/5/21
6045, 1/23/21
6045, 3/8/21
6045, 3/24/21
6036, 3/10/21
6036, 8/19/21

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
899 questions
0 comments No comments
{count} votes

Accepted answer
  1. xps350 381 Reputation points
    2022-06-17T19:31:38.93+00:00

    Depending on your date settings:

    SELECT Project, CurrentDate, DMax("CurrentDate","YourTable","Project=" & [Project] & " AND CurrentDate<#" & [CurrentDate] & "#") AS PreviousDate FROM YourTable  
    

    OR

    SELECT Project, CurrentDate, DMax("CurrentDate","YourTable","Project=" & [Project] & " AND CurrentDate<#" & Format([CurrentDate],'mm/dd/yyyy') & "#") AS PreviousDate FROM YourTable;  
    

    Note that a renamed Date to CurrentDate. Date is a reserved word. Better not use it as a field name.


2 additional answers

Sort by: Most helpful
  1. imodett 61 Reputation points
    2022-06-18T15:09:33.867+00:00

    this looks interesting. I will attempt. Can you explain the reason behind the two separate attempts? You hinted by stating it was depending on my date settings


  2. Gustav 717 Reputation points MVP
    2022-06-22T06:38:25.39+00:00

    It is simpler and faster to use pure SQL and a subquery:

    Select   
        Project,   
        [Date],   
        (Select Max(T.Date)   
        From ExampleTable As T  
        Where T.Project = ExampleTable.Project   
            And T.Date < ExampleTable.Date) AS PreviousDate   
    From   
        ExampleTable  
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.