Gfrktyl

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).

Below solved by:

SELECT Table.AppointmentDate

FROM Table

WHERE (((Table.ClientName=[Client]) AND ((Year=(SELECT Max(Table.Year) AS PreviousYear

FROM Table

WHERE (((Table.Year)<(SELECT MAX(Table.Year) FROM Table WHERE ((Table.ClientName=[Client]))))))));

Now the challenge is to merge this with Client fields (like AppointmentDate) from the Table per the most recent year - and to show this for each client. Is there any way to do this without a UNION?

Well, after a lot of different attempts at just entering SQL into Access, still missing something important:

Here are the Pieces so far (okay, so MAX appears to work despite documentation that said otherwise):

SELECT MAX(Table.Year) 

FROM Table

WHERE Table.Year<(SELECT MAX(Table.Year) FROM Table)

The above correctly selects the previous year (generally for Table).

The previous year for a particular client can be isolated thus:

SELECT Max(Table.Year) AS PreviousYear

FROM Table

WHERE (((Table.Year)<(SELECT MAX(Table.Year) FROM Table WHERE ((Table.ClientName)=[Client]))));

Where "Client" is, for now, just entered manually.

Now the challenge is to select the AppointmentDate value from the previous year's record by way of a common ClientName (and the above PreviousYear value for each ClientName - the PreviousYear values will be different for each Client) - this is where I seem to be missing something important:

SELECT Table.AppointmentDate

FROM Table

WHERE (((Table.ClientName=[Client])) AND ((Table.Year)=(SELECT MAX(Table.Year) AS LastDueDate FROM Table WHERE (((Table.Year)<(SELECT MAX(Table.Year) FROM Table WHERE ((Table.ClientName)=[Client]))))));

Not sure what's missing, tried: FROM Table t ...

Any insights would be appreciated - thanks!