Forum Discussion
Tony2021
Jan 15, 2025Steel Contributor
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_poetzlCopper 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- Tony2021Steel 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- Tony2021Steel Contributor
thanks Karl. that worked perfectly! I did try a Dlookup before posting this question but I wasnt using the Dmax. thanks again!