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

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
Asked:
Keef4000
1 Solution
 
Gustav BrockCIOCommented:
You can use a sum of Sgn([YourValueField])

I would do it in the report.

/gustav
0
 
Keef4000Author Commented:
Ok, but I need to count how many records have a -1 in it then. How do I do that?
0
 
Gustav BrockCIOCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Keef4000Author 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
 
Helen FeddemaCommented:
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
 
mbizupCommented:
--->> 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
 
Keef4000Author Commented:
I tried his formula and it didn't work.
0
 
Gustav BrockCIOCommented:
It does work. What did you do?

/gustav
0
 
Keef4000Author Commented:
I tried these formula's and it didn't return a value

SumofPlus: Abs(Sum([YourValueField]>0))
SumofMinus: Abs(Sum([YourValueField]<0))
0
 
Gustav BrockCIOCommented:
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now