Solved

Report group record counts with criteria

Posted on 2013-10-25
941 Views
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]")

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
Question by:AviationAce
• 4
• 3
• 2

LVL 74

Expert Comment

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

Expert Comment

I use this in THE control source of a text box on reportS to count occurrences :

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

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

Author Comment

@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

LVL 21

Expert Comment

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)

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)

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

LVL 74

Expert Comment

<, 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

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

...and did you try the docunt?
This works fine for me, see the attached sample file

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

Jeffrey Coachman earned 450 total points
Output looks like this:
0

Author Comment

@boag2000

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.

=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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…