Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Microsoft Access Count records formula

Posted on 2013-10-31
Medium Priority
738 Views
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
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

LVL 51

Expert Comment

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

I would do it in the report.

/gustav
0

Author Comment

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

LVL 51

Expert Comment

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

Author Comment

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

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

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

ID: 39622534
I tried his formula and it didn't work.
0

LVL 51

Expert Comment

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

/gustav
0

Author Comment

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 51

Accepted Solution

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

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with â€¦
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
###### Suggested Courses
Course of the Month5 days, left to enroll