Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Microsoft Access Count records formula

Posted on 2013-10-31
10
Medium Priority
?
739 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 52

Expert Comment

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

I would do it in the report.

/gustav
0
 

Author Comment

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

Expert Comment

by:Gustav Brock
ID: 39615405
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:Keef4000
ID: 39615429
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
ID: 39615589
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
 
LVL 61

Expert Comment

by:mbizup
ID: 39615632
--->> 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
ID: 39622534
I tried his formula and it didn't work.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 39626658
It does work. What did you do?

/gustav
0
 

Author Comment

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

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

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 39668248
Did you replace YourValueField with the name of your field?

Or reverse it:

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

/gustav
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

885 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