Query to include the next but maximum record

[Jack Sheet]

Sorry all, I am still in a pickle.

I now have select query that displays (among others) three fields:
ID_Tasks......ID_Clients........EndDate

There are no duplicates of ID_Tasks
ID_Clients does contain duplicates
There are no duplicate values of EndDate for any given value of ID_Clients,
but there could be duplicates provided that ID_Clients differ.

I want a select query that generates the following output:

ID_Tasks......ID_Clients........EndDate......LatestPreviousEndDate

Note that for any given record the EndDate may not be the maximum, but the
LatestPReviousEndDate must be the maximum prior to EndDate, but only of
those records for the specified ID_Clients.

 

[John Spencer]

Sounds like a coordinated subquery is needed in the SELECT clause.

SELECT ID_Tasks, ID_Clients, EndDate
, (SELECT Max(EndDate)
FROM YourTable as Temp
WHERE Temp.Id_Clients = YourTable.Id_Clients
AND Temp.EndDate < YourTable.EndDate) as PreviousEndDate
FROM YourTable

 

[Jack Sheet]

Thanks John. It is like learning a foreign language. After I do enough of
them it will suddenly click

Sounds like a coordinated subquery is needed in the SELECT clause.

SELECT ID_Tasks, ID_Clients, EndDate
, (SELECT Max(EndDate)
FROM YourTable as Temp
WHERE Temp.Id_Clients = YourTable.Id_Clients
AND Temp.EndDate < YourTable.EndDate) as PreviousEndDate
FROM YourTable

 

[Jack Sheet]

Thanks John

I am getting a new style of error message now.

Your solution worked fine. My only problem was that some records returned a
Null result. I expected this, ie for records which containing the earliest
value of EndDate.

For Null results I want a date [DOB] to be obtained from table T_Clients. So
I started started with your query, then raised another query on that one,
supposedly testing for Null entries and substituting (where found) another
date DOB from T_Clients. So, the initial query reads:

SELECT Q_Tasks_Accs.ID_Tasks, Q_Tasks_Accs.ID_Clients, (Select
Max([EndDate]) FROM Q_Tasks_Accs AS Temp
WHERE Temp.ID_Clients = Q_Tasks_Accs.ID_Clients
AND Temp.[EndDate]< Q_Tasks_Accs.[EndDate]) AS PriorEnd
FROM Q_Tasks_Accs;

The above is saved as Q_Tasks_Accs_PriorEnd

And my second query reads:

SELECT Q_Tasks_Accs_PriorEnd.ID_Tasks, Q_Tasks_Accs_PriorEnd.ID_Clients,
IIf([Q_Tasks_Accs_PriorEnd].[PriorEnd] Is
Null,[T_Clients].[DOB],[Q_Tasks_Accs_PriorEnd].[PriorEnd])
AS PriorEndOrDOB
FROM Q_Tasks_Accs_PriorEnd
INNER JOIN T_Clients ON Q_Tasks_Accs_PriorEnd.ID_Clients =
T_Clients.ID_Clients;

This second query saves OK, but when I run it I get an error message:

"MicroSoft Office Access has encountered an error and needs to close. We
are sorry for the inconvenience. etc etc"
I am prompted to repair and reopen the database. That does not good.
I am prompted to send an error report to MicroSoft. That may do some good
in a few months' time but it is no use to me now. Sometimes when this sort
of thing happens you get a link to a resolution, but not this time.

Perhaps I need a different approach?

 

[John Spencer]

Perhaps you can just add T_Clients to your first query or is it already
there?

Another option would be to build a VBA function that would take the client
id, the DOB, and the current end date and return a value.


Or perhaps you can use the DMax function in conjunction with the NZ
function.

Nz(DMAX("EndDate","YourTable","EndDate<#" & [EndDate] & "# AND ID_Clients="
& ID_Clients ),T_Clients.DOB) As PriorDate