Have a Table with ID's assigned to records as they are created.
Have records for multiple years (sometimes years are skipped, so could have: 2019, 2017, 2016, etc...).
Records include a Year field as text and a Name field as text (this Name field is identical across the years) and an AptDate field as Date.
With an Access SQL query would like to show:
the Name, the current Year, the current year's AptDate, the 'previous' year's AptDate (via the Name being identical and the Year being the next most recent year)
Have gotten this far with SQL (Shows 'previous' year's AptDate for an EnteredSampleName):
SELECT Table.AptDate
FROM Table
WHERE (((Table.Name)=[EnteredSampleName]) AND ((Year=(SELECT Max(Table.Year) AS LastYear
FROM Table
WHERE (((Table.Year)<(SELECT MAX(Table.Year) FROM Table WHERE ((Table.Name)=[EnteredSampleName]))))))));
Would like to remove EnteredSampleName and show this 'previous' AptDate for all records (some 'previous' years may be Null as there may be no earlier record for this Name).
Can't seem to crack this nut - any help greatly appreciated.
Thank you for your helpful replies - I will be testing them soon.
In the meantime to answer Lee's request:
Sample Table looks as follows:
ID Name Year AptDate
1 A 2015 2015-02-02
2 B 2015 2015-03-05
3 C 2017 2017-04-04
4 B 2017 2017-06-09
5 C 2018 2018-07-03
6 A 2018 2018-09-10
7 A 2019 2019-01-08
8 D 2019 2019-01-09
Query Result goal:
Name Year AptDate AptDatePrevious
A 2019 2019-01-08 2018-09-10
B 2017 2017-06-09 2015-03-05
C 2018 2018-07-03 2017-04-04
D 2019 2019-01-09
There is no AptDatePrevious for D as expected - hope that clarifies - thanks.