Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

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