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

x
?
Solved

Report group record counts with criteria

Posted on 2013-10-25
9
Medium Priority
?
1,045 Views
Last Modified: 2013-12-27
I have a report where I am using groups.  In one of the fields I use the COUNT function to display the number of records in the current group.

=Count("[Ticket]")

Open in new window


The COUNT function does not let you specify any CRITERIA.  I would like to have a few other fields that count a sub-set of the records in a group.
0
Comment
Question by:AviationAce
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39600595
You need to use Dcount

=Dcount("*", "YourTable", "YourField=" & SomeValue)
if the criteria is numeric
use:
=Dcount("*", "YourTable", "YourField=" & SomeValue)
if the criteria is textual
0
 
LVL 21
ID: 39600685
I use this in THE control source of a text box on reportS to count occurrences :

=Sum([myfield]>0, 1, 0)

Open in new window


Replace [myfield]>0 with any criteria you want to count.
0
 

Author Comment

by:AviationAce
ID: 39601232
@TheHiTechCoach
Is it possible to have the criteria based on a different field?

@boag2000
I thought about using DCount, but that would require me having to create an SQL statement based on what is being used to make each group.  This would be a bit cumbersome and slow.
0
Technology Partners: 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!

 
LVL 21
ID: 39601591
Is it possible to have the criteria based on a different field?
Yes. Even a combination of fields. It is a completely different calculation. You can also have more that one.

The criteria can be any expression that will return true or false.


Example you want to know how many reco9rd are for Oklahoma or OK in the CustomerState field

=Sum([CustomerState]="OK" or [CustomerState]="Oklahoma", 1, 0)

Open in new window


Example if you want the count of orders between $500 and $1000 for CustomerState = OK

=Sum((([CustomerState]="OK") and ([OrderTotal]>=500 and [OrderTotal]<=11000)) , 1, 0)

Open in new window


You could use two different text box to show both of the above if you wanted.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39602017
<, but that would require me having to create an SQL statement based on what is being used to make each group.  This would be a bit cumbersome and slow. >
Not sure what that means...no "SQL" is needed at all.
And the syntax will be the same for each group, so it will be neither slow or cumbersome.


Can you post a specific example?
For example you state:
"I would like to have a few other fields that count a sub-set of the records in a group.
"A few other"?
...as you can see by TheHiTechCoach's alternate approach, you still have to "hardcode" these values using either technique.

But perhaps I am not understanding something, so I will let you continue on with TheHiTechCoach, to avoid confusion.

JeffCoachman
0
 

Author Comment

by:AviationAce
ID: 39608325
I tried using the following code as the ControlSource for a Group Hedder field:
=Count(([Paid]=True)) & "/" & Count(([Paid]=False))

The COUNT is equal regardless of the criteria: EX: 33/33

Also, When I try to put the ",1,0" at the end, I get a "wrong number arguments" error.
=Count(([Paid]=True),1,0)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39609225
...and did you try the docunt?
This works fine for me, see the attached sample file

...However I am still confused about your statement:
I thought about using DCount, but that would require me having to create an SQL statement based on what is being used to make each group.  This would be a bit cumbersome and slow.

Using Dcount in this way does not require SQL, nor is it cumbersome or slow...
...am I missing something?

JeffCoachman
Database183.mdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1800 total points
ID: 39609235
Output looks like this:
Sample output
0
 

Author Comment

by:AviationAce
ID: 39621520
@boag2000
A) Your examples are excellent!

B)  The main problem is my grouping is dynamic on this report.

I have a control that lets me change the field this it is grouped on.  This is so I can get weekly, monthly and/or yearly totals using the same report.

So, starting with your example:
=DCount("Paid","YourTable","Paid=False" & " AND " & "EmpID=" & [EmpID])
There would also have to be date criteria:
=DCount("Paid","YourTable","Paid=False" & " AND " & "EmpID=" & [EmpID] & " AND" & "[DateClosed] >= " & #01/01/2013# & " AND" & "[DateClosed] <= " & 01/31/2013)

Say for example the report is grouped by month, then that criteria changes with each header.

So, how can I use DCount?  I would have to extract the criteria used to produce each group detail/header/footer.
0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

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.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

722 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