Solved

Microsoft Access Count records formula

Posted on 2013-10-31
10
732 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
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 49

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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 49

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 49

Accepted Solution

by:
Gustav Brock earned 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

816 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

11 Experts available now in Live!

Get 1:1 Help Now