Return Value where ID<ID (Nested Query)

Hello Experts, I need to return the [RATE] in tblFXRollsChild WHERE T.IDParentfk=IDParentfk and T.IDRollsPK<IDRollsPK) The below is what I have but I know the WHERE clause is wrong (returns 0 r...

access

Karl_Donaubauer
Jan 15, 2025
Hi,

Try this:

SELECT IDParentfk, IDRollsPK, Rate,

DLookUp("Rate","tblFXRollsChild","IDRollsPK=" & Nz(DMax("IDRollsPK","tblFXRollsChild","IDParentfk=" & [IDParentfk] & " AND IDRollsPK<" & [IDRollsPK]),[IDRollsPK])) AS LastRate

FROM tblFXRollsChild
ORDER BY IDParentfk, IDRollsPK;

Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK

Karl_Donaubauer

MVP

Jan 15, 2025

Hi,

Try this:

SELECT IDParentfk, IDRollsPK, Rate,

DLookUp("Rate","tblFXRollsChild","IDRollsPK=" & Nz(DMax("IDRollsPK","tblFXRollsChild","IDParentfk=" & [IDParentfk] & " AND IDRollsPK<" & [IDRollsPK]),[IDRollsPK])) AS LastRate

FROM tblFXRollsChild
ORDER BY IDParentfk, IDRollsPK;

Servus
Karl
****************
Access Forever News DevCon
Access-Entwickler-Konferenz AEK

Tony2021

Iron Contributor

Jan 15, 2025

thanks Karl. that worked perfectly! I did try a Dlookup before posting this question but I wasnt using the Dmax. thanks again!

Forum Discussion

Return Value where ID<ID (Nested Query)

Resources