Sum function showing negative numbers

[Jackie]

I would like to calculate the number of &quot;Approved&quot; transactions using a query.&nbsp;&nbsp;Since I am performing other calcs in this query, I chose to use the Count function as follows: No_Approvals: count([transaction category]=&quot;Award&quot; And [transaction code]&lt;&gt;&quot;SCR&quot.&nbsp;&nbsp;The &quot;Total&quot; row is set to &quot;Expression&quot;.&nbsp;&nbsp;This returned an incorrect number.<br><br>After playing with the query, I stumbled upon using the Sum function as follows: No_Approvals: Sum([transaction category]=&quot;Award&quot; And [transaction code]&lt;&gt;&quot;SCR&quot.<br><br>This returned the correct number, but it was returned as a negative.<br><br>Any idea why this is happening?&nbsp;&nbsp;Any ideas for a fix.&nbsp;&nbsp;The &quot;counts&quot; will be displayed in a report.&nbsp;&nbsp;I will multiply the count by -1 to display the correct number.&nbsp;&nbsp;I prefer to avoid this &quot;workaround&quot; if possible.<br><br>Thanks for any input.

 

[DougP]

Try breaking apart this line <br>No_Approvals: Sum([transaction category]=&quot;Award&quot; And [transaction code]&lt;&gt;&quot;SCR&quot.<br><br>How can you add a text string?<br>Is [transaction category] a number or a word???<br>&quot;SUM&quot; adds numbers...<br>Also [transaction code] is a String how can you add it.<br>I think Access is confused.<br><br>Count would work but if the answer is wrong then Access is confused.<br>Or <br>Its right and your confused.<br><br><br><br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Jackie]

Doug,<br><br>Thanks for your response.<br><br>[transaction category] and [transaction code] are both fields in the table on which I am basing the query.<br><br>The following is the SQL version:<br><br>SELECT [Transaction Log].[leave year], Left([organization],2) AS org, Count([transaction code] In (&quot;MOE&quot;,&quot;MIE&quot) AS no_members, Sum(IIf([transaction category]=&quot;Donation&quot;,[leave donated/received],0)) AS Hrs_Donated_Members<br>FROM [Transaction Log]<br>GROUP BY [Transaction Log].[leave year], Left([organization],2);<br><br>Access is picking up all of the records that are in the group (organization) but is missing the check to see if the records have a transaction code of &quot;MIE&quot; or &quot;MOE&quot;<br><br><br>

 

[harryrich]

Access boolean values are -1 for true, 0 for false.&nbsp;&nbsp;So the value of the boolean expression you are summing is -1 when true, 0 when false.&nbsp;&nbsp;Consequently, the sum of these will be minus the count you are looking for.&nbsp;&nbsp;Try putting the boolean expression in parentheses and preceeding it with a minus.&nbsp;&nbsp;The sum should then be the count you are looking for.

 

[Jackie]

Thanks.&nbsp;&nbsp;I ended up include *-1 in the report control that displays the sum.