last date or max date issue 1

[danwand]

Hi all,

I have the following query:

-----------------------

SELECT tblCCMR.CMRID, tblClients.SBNo, IIf(IsNull([cmrDate]),"Awaiting 1st Review",[cmrDate]) AS LDate, IIf([confirmed]=True,Format(DateValue([cmrDay] & " " & [monthName] & " " & [cmrNYear]),"ddd dd mmmm yyyy"),IIf(myNull([cmrNMonth]),"Has not been scheduled",Left([monthName],3) & " " & [cmrNYear] & " (day to be confirmed)")) AS NextDate, tblCCMR.cmrReason, tblCCMR.cmrDate
FROM (tblCCMR LEFT JOIN LKUMonths ON tblCCMR.cmrNMonth = LKUMonths.monthID) RIGHT JOIN tblClients ON tblCCMR.SBNo = tblClients.SBNo
ORDER BY tblCCMR.cmrDate DESC;

---------------

Which produces these results:

---------------------------

CMRID SBNo LDate NextDate cmrReason cmrDate
21 21 09/09/2008 Has not been scheduled 09/09/2008
18 18 23/07/2008 Has not been scheduled 23/07/2008
15 16 14/07/2008 Has not been scheduled 14/07/2008
4 1 29/06/2008 Sun 21 December 2008 29/06/2008
7 4 25/06/2008 Has not been scheduled 25/06/2008
12 10 17/06/2008 Has not been scheduled 17/06/2008
5 2 13/06/2008 Has not been scheduled 13/06/2008
9 7 13/06/2008 Has not been scheduled 13/06/2008
20 20 12/06/2008 Has not been scheduled 12/06/2008
23 24 06/06/2008 Has not been scheduled 06/06/2008
13 14 04/05/2008 Has not been scheduled 04/05/2008
19 19 11/04/2008 Has not been scheduled 11/04/2008
10 8 04/04/2008 Has not been scheduled 04/04/2008
11 9 14/03/2008 Has not been scheduled 14/03/2008
8 6 13/03/2008 Has not been scheduled 13/03/2008
17 18 20/02/2008 Mon 23 July 2007 20/02/2008
6 3 07/02/2008 Has not been scheduled 07/02/2008
3 1 15/12/2007 Sun 29 June 2008 15/12/2007
14 15 05/10/2007 Has not been scheduled 05/10/2007
22 22 14/09/2006 Has not been scheduled 14/09/2006
2 1 11/05/2006 Fri 15 December 2006 11/05/2006
13 Awaiting 1st Review Has not been scheduled
5 Awaiting 1st Review Has not been scheduled
1 1 Awaiting 1st Review Thu 11 May 2006
12 Awaiting 1st Review Has not been scheduled
26 Awaiting 1st Review Has not been scheduled
17 Awaiting 1st Review Has not been scheduled
23 Awaiting 1st Review Has not been scheduled
25 Awaiting 1st Review Has not been scheduled
11 Awaiting 1st Review Has not been scheduled

----------------------------

I want to then create a query that returns only 1 row for each SBNO that shows the values of the lastest LDate and its corrsponding nextDate.

I want the query to produce the following results:

----------------------------------------

CMRID SBNo LDate NextDate cmrReason cmrDate
21 21 09/09/2008 Has not been scheduled 09/09/2008
18 18 23/07/2008 Has not been scheduled 23/07/2008
15 16 14/07/2008 Has not been scheduled 14/07/2008
4 1 29/06/2008 Sun 21 December 2008 29/06/2008
7 4 25/06/2008 Has not been scheduled 25/06/2008
12 10 17/06/2008 Has not been scheduled 17/06/2008
5 2 13/06/2008 Has not been scheduled 13/06/2008
9 7 13/06/2008 Has not been scheduled 13/06/2008
20 20 12/06/2008 Has not been scheduled 12/06/2008
23 24 06/06/2008 Has not been scheduled 06/06/2008
13 14 04/05/2008 Has not been scheduled 04/05/2008
19 19 11/04/2008 Has not been scheduled 11/04/2008
10 8 04/04/2008 Has not been scheduled 04/04/2008
11 9 14/03/2008 Has not been scheduled 14/03/2008
8 6 13/03/2008 Has not been scheduled 13/03/2008
6 3 07/02/2008 Has not been scheduled 07/02/2008
14 15 05/10/2007 Has not been scheduled 05/10/2007
22 22 14/09/2006 Has not been scheduled 14/09/2006
13 Awaiting 1st Review Has not been scheduled
5 Awaiting 1st Review Has not been scheduled
12 Awaiting 1st Review Has not been scheduled
26 Awaiting 1st Review Has not been scheduled
17 Awaiting 1st Review Has not been scheduled
23 Awaiting 1st Review Has not been scheduled
25 Awaiting 1st Review Has not been scheduled
11 Awaiting 1st Review Has not been scheduled

------------------------------------

I have tried the following query that i adapted from another query in my datebase:

-------------------------

SELECT A.SBNo, A.LDate, A.NextDate
FROM qryVCMRL1 AS A INNER JOIN [SELECT SBNO, Max(LDate) AS MaxDate
FROM qryVCMRL1 GROUP BY SBNO
]. AS B ON (A.SBNo = B.SBNO) AND (A.LDate = B.MaxDate);

-------------------------

This produces the following incorrect results:

--------------------------


SBNo LDate NextDate
1 Awaiting 1st Review Thu 11 May 2006
2 13/06/2008 Has not been scheduled
3 07/02/2008 Has not been scheduled
4 25/06/2008 Has not been scheduled
5 Awaiting 1st Review Has not been scheduled
6 13/03/2008 Has not been scheduled
7 13/06/2008 Has not been scheduled
8 04/04/2008 Has not been scheduled
9 14/03/2008 Has not been scheduled
10 17/06/2008 Has not been scheduled
11 Awaiting 1st Review Has not been scheduled
12 Awaiting 1st Review Has not been scheduled
13 Awaiting 1st Review Has not been scheduled
14 04/05/2008 Has not been scheduled
15 05/10/2007 Has not been scheduled
16 14/07/2008 Has not been scheduled
17 Awaiting 1st Review Has not been scheduled
18 23/07/2008 Has not been scheduled
19 11/04/2008 Has not been scheduled
20 12/06/2008 Has not been scheduled
21 09/09/2008 Has not been scheduled
22 14/09/2006 Has not been scheduled
23 Awaiting 1st Review Has not been scheduled
24 06/06/2008 Has not been scheduled
25 Awaiting 1st Review Has not been scheduled
26 Awaiting 1st Review Has not been scheduled

-------------------------------

So i get the desired 1 row per SBNO but the returned dates are wrong for SBNO 1
i.e.
1 Awaiting 1st Review Thu 11 May 2006

should actually be:

1 29/06/2008 Sun 21 December 2008


Could anyone suggest where i'm going wrong or give me an alternative solution.

Thanks
Dan

 

[PHV]

What about this ?

SELECT A.SBNo, A.LDate, A.NextDate
FROM qryVCMRL1 AS A INNER JOIN (
SELECT  SBNO, Nz(Max(cmrDate),0) AS MaxDate FROM qryVCMRL1 GROUP BY  SBNO
) AS B ON A.SBNo = B.SBNo AND Nz(A.cmrDate,0) = B.MaxDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[danwand]

Once again PH you have saved the day, works perfectly.


Thanks
Dan