Oct 04 2022 06:21 AM - edited Oct 04 2022 07:41 AM
Experts,
I have an aggregate query as below (and attached file) that returns the LAST record on RATE.
I need to somehow adjust it to use the LAST of RATE of the non zero number.
You can see the LAST of the rate is 0 for the highlighted one below:
there are 5 records for IDParentfk (refer to the count) and I need to return the Rate that is NOT ZERO.
So essentially, I still need the LAST record but ONLY the LAST of where Rate is NOT ZERO.
tblFXRollsChild:
5 records.
I need the 3rd record in this case since its the LAST that is not 0.
SQL:
SELECT tblFXRollsChild.IDParentfk, Last(tblFXRollsChild.Rate) AS LastOfRate, Count(tblFXRollsChild.IDRollsPK) AS CountOfIDRollsPK
FROM tblFXRollsChild
GROUP BY tblFXRollsChild.IDParentfk;
let me know if there are any questions. I hope its clear.
Please see attached sample db
Oct 04 2022 09:19 AM
Oct 06 2022 06:26 AM
SolutionHi,
How about this?
SELECT T1.IDParentfk, T1.Rate
FROM tblFXRollsChild AS T1
WHERE T1.IDRollsPK=(SELECT Max(IDRollsPK) FROM tblFXRollsChild WHERE Rate<>0 AND IDParentfk=T1.IDParentfk)
ORDER BY T1.IDParentfk
Servus
Karl
************
Access News
Access DevCon
Oct 06 2022 04:55 PM
Oct 06 2022 06:26 AM
SolutionHi,
How about this?
SELECT T1.IDParentfk, T1.Rate
FROM tblFXRollsChild AS T1
WHERE T1.IDRollsPK=(SELECT Max(IDRollsPK) FROM tblFXRollsChild WHERE Rate<>0 AND IDParentfk=T1.IDParentfk)
ORDER BY T1.IDParentfk
Servus
Karl
************
Access News
Access DevCon