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

josef_poetzl

Copper Contributor

Jan 20, 2025

Hi,

Perhaps an additional SQL variant that only requires 1 query per record:

SELECT
    T.idParentfk, T.IDRollsPK, T.Rate
    , Nz(
         (
             Select Top 1 Rate from tblFXRollsChild as X 
             Where X.IDParentfk = T.idParentfk and X.IDRollsPK < T.IDRollsPK 
             Order By X.IDRollsPK desc)
        , T.Rate) as RatePrev
FROM tblFXRollsChild as T
ORDER BY IDParentfk, IDRollsPK

Kind regards,
Josef

Tony2021

Iron Contributor

Jan 21, 2025

Nice Josef. Now I see how a different approach. I thought I could use Top 1 but didnt know how to put it together. thank you. I will put it in my book of secrets. thank you for the follow up!

Forum Discussion

Return Value where ID<ID (Nested Query)

Resources