Have a table with the IDs assigned to the records as they are created.
Have records for several years (sometimes years are omitted, so there could be: 2019, 2017, 2016, etc …).
The records include a Year field as text and a Name as text field (this Name field is identical in all years) and a Date Suitable field as Date.
With an Access SQL query, you would like to show:
the Name, the current Year, the Date of the Year of the current year, the Date of Fitness of the previous year (through which the Name is identical and the Year is the next most recent year)
Having arrived here with SQL (Shows AptDate from the previous year for an EnteredSampleName):
SELECT Table.AptDate From the table WHERE (((Table.Name) =[EnteredSampleName]) AND ((Year = (SELECT Max (Table.Year) AS LastYear) From the table WHERE (((Table.Year) <(SELECT MAX (Table.Year) FROM Table WHERE ((Table.Name) =[EnteredSampleName])))))))));
I would like to remove EnteredSampleName and show this AptDate & # 39; previous & # 39; for all records (some years & # 39; previous & # 39; may be null, since there may not be a previous record for this name).
It seems I can not break this nut – any help much appreciated.