Complex query

[gcosta]

Complex query

Dear friends, I have the following table:


Table: XY



ID

UD



1

AAA



2

BBB



3

CCC



4

DDD




Does anyone know the SQL expression or query, to get the answer to: "CF" below?



Query



ID

UD

CF



1

AAA





2

BBB

AAA



3

CCC

BBB



4

DDD

CCC





I tried this:
SELECT XY.ID, (DLookUp("[UD]","XY",[ID]=([ID]-"1"))) AS CF, XY.UD
FROM XY;
But it didn't work

[Bob Phillips]

Can't you do an embedded select


SELECT XY.ID
,XY.UD
,(SELECT xy2.UD FROM XY WHERE xy2.ID = XY.ID-1) AS CF
FROM XY

[gcosta]

Thank you friends, worked with the query:
SELECT XY.ID, XY.UD, (SELECT xy2.UD FROM XY WHERE xy2.ID = XY.ID-1) AS CF
FROM XY, XY AS XY2;

[Bob Phillips]

I made a slight error, what I really meant was


SELECT XY.ID
,XY.UD
,(SELECT xy2.UD FROM XY AS xy2 WHERE xy2.ID = XY.ID-1) As CF
FROM XY

Does that work?

[gcosta]

I made a slight error, what I really meant was


SELECT XY.ID
,XY.UD
,(SELECT xy2.UD FROM XY AS xy2 WHERE xy2.ID = XY.ID-1) As CF
FROM XY

Does that work?

worked thank you

[gcosta]

worked thank you