none
Count money field >0 in a summary query (Access 2016). RRS feed

  • Question

  • I'm trying to get count of a money field where it's greater than zero in a summary query in Access 2016.  The SQL currently is

    SELECT tblPP.[G/FBPatt], tblResult.RNum, Sum(tblResult.[W$PP]) AS [SumOfW$PP], Sum(tblResult.[PL$PP]) AS [SumOfPL$PP], Sum(tblResult.[SH$PP]) AS [SumOfSH$PP], Count(tblResult.[W$PP]) AS [CountOfW$PP], Count(tblResult.[PL$PP]) AS [CountOfPL$PP], Count(tblResult.[SH$PP]) AS [CountOfSH$PP]
    FROM tblPP INNER JOIN tblResult ON tblPP.PPID = tblResult.PPID
    GROUP BY tblPP.[G/FBPatt], tblResult.RNum
    ORDER BY tblPP.[G/FBPatt] DESC , tblResult.RNum;

    The counts that I have in there now give the the total number of records by the groupings, but I need additional field(s) where it counts W$PP >0, for example and have it display in the SAME query, if possible.  That count will then be used for additional calculations.  It seems simple, but I'm either getting an error that it's not part of an aggregate function, or it just produces the same number as the counts above, which does not match the actual data.

    I'm just getting back into Access after not using it since 2006, and would appreciate any help you can give.  Thank you.

    Richard

    Tuesday, June 7, 2016 3:05 PM

Answers

  • Hi,

    try sth like this in SQL:

    Abs(Sum([W$PP]>0)) AS [CountOfW$PPGreaterZero]

    or if you prefer Count:

    Count(IIf([W$PP]>0,[W$PP],Null)) AS [CountOfW$PPGreaterZero]

    BTW You should avoid the use of special signs like "$" and "/" in field or object names. They afford brackets and may cause trouble, e.g. errors in SQL, VBA and expressions that are hard to track down.


    cu
    Karl
    Access FAQ (de/it): donkarl.com
    Access Lobby: AccessDevelopers.org

    Tuesday, June 7, 2016 4:08 PM
  • Hi rwh5757-1,

    Here I think suggestion given by the Karl Donaubauer can be work for you.

    I have tested it and it gives result as per your requirement.

    so please check the suggestion given by him and let us know it worked for you or not.

    if you think that suggestion worked for you I would recommend you to mark the suggestion as an Answer.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by rwh5757-1 Wednesday, June 8, 2016 1:45 AM
    Wednesday, June 8, 2016 12:22 AM
    Moderator

All replies

  • Hi,

    try sth like this in SQL:

    Abs(Sum([W$PP]>0)) AS [CountOfW$PPGreaterZero]

    or if you prefer Count:

    Count(IIf([W$PP]>0,[W$PP],Null)) AS [CountOfW$PPGreaterZero]

    BTW You should avoid the use of special signs like "$" and "/" in field or object names. They afford brackets and may cause trouble, e.g. errors in SQL, VBA and expressions that are hard to track down.


    cu
    Karl
    Access FAQ (de/it): donkarl.com
    Access Lobby: AccessDevelopers.org

    Tuesday, June 7, 2016 4:08 PM
  • Hi rwh5757-1,

    Here I think suggestion given by the Karl Donaubauer can be work for you.

    I have tested it and it gives result as per your requirement.

    so please check the suggestion given by him and let us know it worked for you or not.

    if you think that suggestion worked for you I would recommend you to mark the suggestion as an Answer.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by rwh5757-1 Wednesday, June 8, 2016 1:45 AM
    Wednesday, June 8, 2016 12:22 AM
    Moderator
  • Karl,

    Thank you for the Sum and the Count answer, it worked perfectly.  I also took out the special characters in the field names as you suggested.  I'm now trying to use the count in division to get a percent, but only want to display to two places past the decimal point in the final answer displayed in the query.  I used an example of the Convert function on the site, however the example did not have an IIf statement in it, like mine does.  I got it to work without the convert function, but not with it yet.  I also tried to find a table that explains what the "5" represents in the convert decimal function, not sure.  How can I make this work?  Thank you for your help.

    Richard

    SELECT tblPP.GFBPatt, tblResult.RNum, Sum(tblResult.WPP) AS SumOfWPP, Sum(tblResult.PLPP) AS SumOfPLPP, Sum(tblResult.SHPP) AS SumOfSHPP, Count(tblResult.WPP) AS CountOfWPP, Count(tblResult.PLPP) AS CountOfPLPP, Count(tblResult.SHPP) AS CountOfSHPP, Count(IIf([WPP]>0,[WPP],Null)) AS [CountOfWPPGreaterZero], Count(IIf([PLPP]>0,[PLPP],Null)) AS [CountOfPLPPGreaterZero], Count(IIf([SHPP]>0,[SHPP],Null)) AS [CountOfSHPPGreaterZero], 

    (IIf([CountOfWPPGreaterZero] >0,(Convert(Decimal(5,2), ([CountOfWPPGreaterZero]))/ Convert(Decimal(5,2), CountOfWPP)), Convert(Decimal(5,2), 0))) AS WPercent 

    FROM tblPP INNER JOIN tblResult ON tblPP.PPID = tblResult.PPID
    GROUP BY tblPP.GFBPatt, tblResult.RNum
    ORDER BY tblPP.GFBPatt DESC , tblResult.RNum;


    • Edited by rwh5757-1 Saturday, June 11, 2016 6:45 AM
    Saturday, June 11, 2016 6:39 AM
  • Karl, or anyone else,

    I solved the decimal formatting problem by setting the Properties of the calculated field in the query, so I don't need an answer.  Thank you again.

    Richard

    Sunday, June 12, 2016 4:52 AM