Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Oct 04, 2022

Aggregate Qry - Use LAST of Non Zero Number

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

  • Hi,

     

    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

  • Tony2021's avatar
    Tony2021
    Steel Contributor
    there might be a better way to do this though. I am open.
  • Hi,

     

    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

    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Worked perfectly! thank you Karl. Nested query. Scary.

Resources