• Status: Solved
• Priority: Medium
• Security: Public
• Views: 745

# Microsoft Access Count records formula

So I am running a report that I want to be able to count the records of a specific value in a column.

I need to have it return to me how many records have a negative number in the column and how many have a positive number on a access report. Would I do this in the query or would it be in the report?
0
Keef4000
1 Solution

CIOCommented:
You can use a sum of Sgn([YourValueField])

I would do it in the report.

/gustav
0

Author Commented:
Ok, but I need to count how many records have a -1 in it then. How do I do that?
0

CIOCommented:
You are right. You have to filter for the sign, so Sgn is not needed:

SumofPlus: Abs(Sum([YourValueField]>0))
SumofMinus: Abs(Sum([YourValueField]<0))

/gustav
0

Author Commented:
does seem to work. States that there is an extra "(" in the expression but I don't see an extra one in yours or mine.
0

Commented:
I think you need to do a Count, not a Sum, if the intention is to count how many positive and how many negative values there are.  Otherwise you get a sum of the values, which is different.
0

Commented:
--->> I think you need to do a Count, not a Sum

Gustav (cactus_data) is correct... he's Summing the results of comparisons ... (boolean values: True/False,  0 and -1 respectively), which in effect gives you the *count* of records meeting those criteria.
0

Author Commented:
I tried his formula and it didn't work.
0

CIOCommented:
It does work. What did you do?

/gustav
0

Author Commented:
I tried these formula's and it didn't return a value

SumofPlus: Abs(Sum([YourValueField]>0))
SumofMinus: Abs(Sum([YourValueField]<0))
0

CIOCommented:
Did you replace YourValueField with the name of your field?

Or reverse it:

SumofPlus: Sum(Abs([YourValueField]>0))

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.