2nd Most Recent Date Problem

[edwin.villamayor]

Hi All,

As usual, I'm stuck with another problem that I can't solve. I have a
table called MainTable (see below):

MainTable:

Loan RevDate
1234 12/21/06
5678 10/08/06
5678 09/05/05
1234 02/28/04
6589 03/15/05
7856
1234 06/05/02

I created another table called CurrentTable with fields called Loan,
MostRecentRevDate, and 2ndMostRecentRevDate. I was able to update the
Loan and MostRecentRevDate fields by using the MAX(Date) function (see
below).

CurrentTable:

Loan MostRecentRevDate 2ndMostRecentRevDate
1234 12/21/06
5678 10/08/06
6589 03/15/05
7856

My problem is that I don't know how to update the 2ndMostRecentRevDate
field (i.e. the 2ndMostRecentRevDate = 2nd Most Recent RevDate found in
the MainTable) that looks like the following:

Loan MostRecentRevDate 2ndMostRecentRevDate
1234 12/21/06 02/28/04
5678 10/08/06 09/05/05
6589 03/15/05
7856

As usual, any help would be most appreciated.

Thanks in advance.

Edwin

 

[Marshall Barton]

As usual, I'm stuck with another problem that I can't solve. I have a
table called MainTable (see below):

MainTable:

Loan RevDate
1234 12/21/06
5678 10/08/06
5678 09/05/05
1234 02/28/04
6589 03/15/05
7856
1234 06/05/02

I created another table called CurrentTable with fields called Loan,
MostRecentRevDate, and 2ndMostRecentRevDate. I was able to update the
Loan and MostRecentRevDate fields by using the MAX(Date) function (see
below).

CurrentTable:

Loan MostRecentRevDate 2ndMostRecentRevDate
1234 12/21/06
5678 10/08/06
6589 03/15/05
7856

My problem is that I don't know how to update the 2ndMostRecentRevDate
field (i.e. the 2ndMostRecentRevDate = 2nd Most Recent RevDate found in
the MainTable) that looks like the following:

Loan MostRecentRevDate 2ndMostRecentRevDate
1234 12/21/06 02/28/04
5678 10/08/06 09/05/05
6589 03/15/05
7856

Don't put that kind of thing in a table, it will be wrong as
soon as another revision comes along. Instead you should
calculate it in a query so it is up to date every time you
run the query.

SELECT T.Loan, Max(T.RevDate) As MostRecentRevDate,
Max(X.RevDate) As 2ndMostRecentRevDate
FROM MainTable As T LEFT JOIN MainTable As X
ON T.Loan = X.Loan
AND X.RevDate < T.RevDate
GROUP BY T.Loan

 

[edwin.villamayor]

Thanks Marsh. Unfortunately, in reality, aside from the MainTable,
there are about 30 other tables that update the CurrentTable and I
didn't want to create too many joins. So, I really can't take apart
the table - anyway, I built a macro to run all the updates. I'll give
your suggestion a try and thanks again for the help.

Edwin