Can I show the value of the NEXT record in a query? 1

[Hanss]

I have a table "test" with the following fields and data:

id: date:
1 16.01.2006
2 17.01.2006
3 19.01.2006
4 21.01.2006


Is it possible to have a query with an additional field "date2" which displays the "date" value from the next record?

Something like this:

SELECT test.id, test.date, ([nextrecord].[date]) AS date2
FROM test;

Would appreciate any help.

kind regards,
Hanss
Zurich

 

[alvechurchdata]

You could try joining the table with itself with something like:
[TT]
... WHERE a.id = b.id+1
[/TT]
but you'll have to do something at the end of the table to avoid referring to the record that's after the last record.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Hanss]

It worked!

Thank you for showing me the right direction.

Hanss

 

[alvechurchdata]

It worked!

Probably the two nicest words in a programmer's vocabulary<g>. Glad I could help and thanks for the star.

Geoff Franklin

http://www.alvechurchdata.co.uk