All the advice I was able to locate doesn't seem to get at this (I am also trying to avoid a DLookup in the Table itself):
The table: Table has client records including a Year field, an AppointmentDate field and a ClientName field (which has identical values for each individual client across all years). (Table's client IDs for each record are expectedly random.)
In an MS Access Query that shows a sampling of this year's clients (by ClientName and delimited by a few other factors), need to also show the value of AppointmentDate from the previous year's (max Year minus 1) record for the same client.
Subquery attempts such as the below do not work:
LastYearApptDate:
(SELECT AppointmentDate FROM (SELECT (AppointmentDate) FROM Table ORDER BY Year DESC LIMIT 2) ORDER BY AppointmentDate ASC LIMIT 1)
Nor have the online suggested Top attempts shown the expected result.
(I realize Max is not supported in MS Access.)
Perhaps I am missing something about Access's syntax for a SQL subquery (via the "Zoom" Dialogue).