Solved

Microsoft Access Count records formula

Posted on 2013-10-31
10
736 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 50

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 50

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 50

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 50

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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