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.

  • Can you post some sample data?
    – Lee Mac
    Mar 24 '19 at 16:59

The numbered examples from this MS site might point you in the right direction:

What you will need to do is something like this:

Select Table.Name, Table.Year, Table.AptDate, expr(calculate previous date here*)
from Table
Order by {however you want}

*The expression would look something like this:

Select max(PrevAptDate.AptDate
from Table as PrevAptDate
where (PrevAptDate.AptDate < Table.AptDate)
(PrevAptDate.Name = Table.Name)

This is all off the cuff, but it should be close and get you pointed in the right direction. I hope it helps.

  • 1
    Thank you - this was the quickest solution to the problem from within Access.
    – Stumped
    Mar 25 '19 at 15:35

Assuming I've correctly understood your setup & requirements, below is one possible method using joins as opposed to correlated subqueries:

select t0.name, t0.year, t0.aptdate, q2.aptdate
from YourTable t0 left join
    select t1.name, t1.aptdate
    from YourTable t1 inner join
        select t2.name, max(t2.year) as lastyear
        from YourTable t2
        where t2.year < year(date())
        group by t2.name
    ) q1 on
    t1.name = q1.name and 
    t1.year = q1.lastyear
) q2 on 
t0.name = q2.name
where t0.year = year(date())

In the above query, replace every occurrence of YourTable with the name of your table.

Here, the innermost query q2 obtains the greatest year for each name where the year is less than the current year year(date()).

The result of this query is then inner joined with the original table in order to select the corresponding aptdate for each name & maximum year. This forms the subquery q2.

Finally, the original table is left joined to the subquery q2 (in case there is no previous year record for a given name), and the results are output by the parent query.

  • Definitely the bigger picture elegant solution. Just a note that this one assumes that we have records from the current year - but that is totally my fault for not being clear enough about the Table data initially.
    – Stumped
    Mar 25 '19 at 15:33
  • If I could select two answers as the best - I would definitely want to select this one too.
    – Stumped
    Mar 25 '19 at 15:37
  • @Stumped You're most welcome - an upvote is an alternative to accepting a solution :)
    – Lee Mac
    Mar 25 '19 at 18:18
  • 1
    Still learning the ropes ;) - upvote absolutely deserved!
    – Stumped
    Mar 25 '19 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.