Find second to last date

[jzwp22]

thanks for your interest... so to solve my problem I have to create two queries? I will try as you suggest...

I split it into two queries just to make things a little easier. You can of course, nest the queries. If you want to do that, the query would look like this

SELECT qry2mostrecent.ID, Min(qry2mostrecent.Startdate) AS MinOfStartdate
FROM (SELECT vwAttendances.ID, vwAttendances.SchName, vwAttendances.STARTDATE
FROM tbl_05 INNER JOIN vwAttendances ON tbl_05.ID = vwAttendances.ID
WHERE (((vwAttendances.STARTDATE) In (SELECT TOP 2 T.STARTDATE FROM vwAttendances T WHERE T.ID = vwAttendances.ID ORDER BY T.STARTDATE Desc)))
ORDER BY vwAttendances.STARTDATE ) qry2mostrecent
GROUP BY qry2mostrecent.ID

 

[skizzly]

hi mdlueck,

my query following your revision:

SELECT top 1 vwAttendances.ID, vwAttendances.SchName, vwAttendances. SchName, vwAttendances.STARTDATE

FROM tbl_05 INNER JOIN vwAttendances ON tbl_05.ID = vwAttendances.ID

the above line is used to bring in the students needed to find the second to last attendance


WHERE (((vwAttendances.STARTDATE) In (SELECT TOP 2 T.STARTDATE FROM vwAttendances T ORDER BY T.STARTDATE Desc)))

ORDER BY vwAttendances.STARTDATE;

I ran this query and it returned nothing....

 

[mdlueck]

Ssssiiiggghhh...

1) For the outer query you should not specify physical real table, you should be specifying the table reference created by the inner query.

2) Do you really have two tables you need to join? If so, you need to do all operations against real tables in the inner query. The outer query will only select record(s) from the inner query's results.

 

[skizzly]

mdlueck, thanks for your time.... with the help of jzwp22 i have got the solution for this problem using the method described.


Thanks all again for assistance.

 

[jzwp22]

You're welcome.