Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Jan 15, 2025

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 records).  I dont know if I need to be using "TOP 1". 

do you see where I am wrong?  

SELECT T1.IDParentfk, T1.IDRollsPK, T1.Rate AS LastOfRate
FROM tblFXRollsChild AS T1
WHERE (((T1.Rate)=(SELECT [RATE] FROM tblFXRollsChild AS T WHERE T.IDParentfk=IDParentfk and T.IDRollsPK<IDRollsPK)))
ORDER BY T1.IDParentfk, T1.IDRollsPK;

In the below tblFXRollsChild, I want to return the RATE for the record above but where there is not a record then return the RATE of the current record. 

 

 

  • 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's avatar
    josef_poetzl
    Copper Contributor

    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's avatar
      Tony2021
      Steel Contributor

      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!

  • 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's avatar
      Tony2021
      Steel Contributor

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

Resources