Second from the LAST Query

  • Thread starter Thread starter Scott Duncan
  • Start date Start date
S

Scott Duncan

I am trying in vain to create a Query that pulls in the Second from the Last
Date.

For Example, there are numerous entries for the 'Calibration Date' for a
specific Serial Number.

I want to know what the Second from the last Calibration Date... 11/29/2001
in this case.

Calibration Dates Serial Number Service Date Calibrated Id
00000002 5/10/2002 Yes 63819
00000002 11/29/2001 Yes 57729
00000002 5/4/2001 Yes 52855
00000002 11/21/2000 Yes 48132
00000002 5/11/2000 Yes 42801
00000002 11/16/1999 Yes 35658
00000002 5/18/1999 Yes 29879
00000002 12/16/1998 Yes 23669
00000002 5/12/1998 Yes 17564
00000002 11/17/1997 Yes 13936
00000002 5/7/1997 Yes 10121



Thanks,

SD
 
Hi,


SELECT a.Calibration, a.Dates, LAST(a.WhateverElse1),
LAST(a.WhateverElse2)
FROM myTable As a INNER JOIN myTable As b
ON a.Dates<= b.Dates
GROUP BY a.Calibration, a.Dates
HAVING COUNT(*) = 2



Another solution is to make a top 2 then, a top 1 in reverser order (mainly
if you want that second position only for one given specific "calibration",
rather than for all possible calibrations, as it is done in the first case).





Hoping it may help,
Vanderghast, Access MVP
 
Thanks, I'll give it a try...

SD
Michel Walsh said:
Hi,


SELECT a.Calibration, a.Dates, LAST(a.WhateverElse1),
LAST(a.WhateverElse2)
FROM myTable As a INNER JOIN myTable As b
ON a.Dates<= b.Dates
GROUP BY a.Calibration, a.Dates
HAVING COUNT(*) = 2



Another solution is to make a top 2 then, a top 1 in reverser order (mainly
if you want that second position only for one given specific "calibration",
rather than for all possible calibrations, as it is done in the first case).





Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
Back
Top