0

MS Access. I'm querying a table that displays a client_name, release_name, release_date for a number of clients and releases. I'm trying to write a query that returns the following data for one client: client_name, release_name, from_release_date, to_release_date. In this case the from_release_date is the original release_date from the underlying table but the new field in the query to_release_date is the day before the next release was installed. For example:

+-------------+-----------+-------------------+-------------------+  
| client_name | release   | from_release_date | to_release date   |  
+-------------+-----------+-------------------+-------------------+  
| client A    | release 1 | 01/01/2017        |   01/02/2017      |  
| client A    | release 2 | 02/02/2017        |   02/03/2017      |  
| client A    | release 3 | 03/03/2017        |                   |  
+-------------+-----------+-------------------+-------------------+

How do I create this 'to_release_date' field?

June7
  • 19,874
  • 8
  • 24
  • 34
bruno.e17
  • 1
  • 1
    Possible duplicate of [DATEADD Calculation](https://stackoverflow.com/questions/24669624/dateadd-calculation) – June7 Jul 02 '17 at 16:59
  • Or duplicate of https://stackoverflow.com/questions/42629523/access-get-value-from-previous-record – June7 Jul 02 '17 at 17:07
  • Dates appear to be in ddmmyyyy (international) format. Review http://allenbrowne.com/ser-36.html and http://allenbrowne.com/subquery-01.html#AnotherRecord – June7 Jul 02 '17 at 17:55

1 Answers1

0

Use a subquery:

Select
    client_name, 
    release_name, 
    release_date As from_release_date,
    (Select Top 1
        DateAdd("d", -1, T.release_date)
    From
        YourTable As T
    Where
        T.client_name = YourTable.client_name
        And
        T.release_date > YourTable.release_date
    Order By
        T.release_date Desc) As to_release_date
    From
        YourTable
Gustav
  • 53,498
  • 7
  • 29
  • 55