previous record in query

[avacess]

hi, from the beginning you must know that i am a beginner with access.

i come with a very old issue, i saw a lot of threads, but nothing fits my case to solve the below problem:
i built the following query:
task id - perform at date
1002 - 06/23/2011
1002 - 06/11/2011
1002 - 06/14/2011
1004 - 05/19/2011
1004 - 06/25/2011
...
task id - defined as text field
perform at date - date/time

if i select for 'perform at date' in query design 'Total' to show 'Last', the last perform at date will be displayed for each task:
1002 - 06/23/2011
1004 - 06/25/2011
.....

my problem is that i want to find a solution to show the previous date to last / second to last date:
for better understanding:
1002 - 06/14/2011
1004 - 05/19/2011
....

do you have a hint for this? i am not familiar with sql, vba at all.



i have to mention that i saw a lot of threads: boblarson made a ref to The_Doc_Man for one of his threads in http://www.access-programmers.co.uk/forums/showthread.php?t=133770 and many others, but none fits to my knowledge and current case.


please let me know if you have any solution.
10x,
george

 

[Taruz]

select  tt.[task id], (
      select min([perform at date]) from (
                           select  table_name.[task id], table_name.[perform at date]
                                                            from  table_name
                              where  table_name.[perform at date] in (
              select top 2 [perform at date] from table_name as trz 
                              where table_name.[task id]=trz.[task id] order by [perform at date] desc)
              order by  table_name.[perform at date]) as tp where tp.[task id]=tt.[task id]) as  [second to last date]
from table_name AS tt
group by tt.[task id]

Hello..

A query in this structure, gives the desired result.. table_name instead, type the name of your table..

 

[avacess]

Taruz,

your a star!!! thank you for your solution. this is what i was looking for.
george