Solved

Microsoft Access Count records formula

Posted on 2013-10-31
10
724 Views
Last Modified: 2013-11-26
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
Comment
Question by:Keef4000
10 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
You can use a sum of Sgn([YourValueField])

I would do it in the report.

/gustav
0
 

Author Comment

by:Keef4000
Comment Utility
Ok, but I need to count how many records have a -1 in it then. How do I do that?
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
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 Comment

by:Keef4000
Comment Utility
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
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
--->> 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 Comment

by:Keef4000
Comment Utility
I tried his formula and it didn't work.
0
 
LVL 49

Expert Comment

by:Gustav Brock
Comment Utility
It does work. What did you do?

/gustav
0
 

Author Comment

by:Keef4000
Comment Utility
I tried these formula's and it didn't return a value

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

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
Did you replace YourValueField with the name of your field?

Or reverse it:

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

/gustav
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now