[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Count on group footer

Posted on 2015-01-29
Medium Priority
103 Views
Hi Experts,
I'm working on a report and having problem to count on the group footer.  In the report, I have Date, TagNo, Chair (store as "C" in the table), Desk (store as "D" in the table).  My report only show Date, TagNo, Chair, Desk but group by Date.  so the Date has header and footer. here is the report look like:

1/2/15 (date)
C0001 (TagNo)          Chair (C)
C0002                         Desk (D)
C0003                         Chair (C)

I want to count how many chair and how many desk on this date, so on the Date footer will show : Total of Chair : 2  Total of Desk : 1

here is the code I put in the control source of the Text box on the Date footer:
Total of Chair: =Count([Type]="C")
Total of Desk: =Count([TYPE]="D")

but both show as 2 not one is 2 and one is 1, do not know why it happens.

Thanks,
0
Question by:urjudo
• 4
• 2

LVL 74

Expert Comment

ID: 40577704
Try something like this in the Date footer:

=Dcount("Type","YourTable","YourDate=" & "#" & YourDate & "#" & " AND Type='C'")
=Dcount("Type","YourTable","YourDate=" & "#" & YourDate & "#" & " AND Type='D'")

JeffCoachman
0

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1600 total points
ID: 40577711
Use:

=Sum(IIF([Type]="C",1,0))

and:

=Sum(IIF([Type]="D",1,0))

Jim.
0

LVL 58

Expert Comment

ID: 40577717
Count(<expression>) just tally's up the count of records where a field (or fields) is not null.

Jim.
0

LVL 74

Expert Comment

ID: 40577719
What *exactly* is stored in the Type field?
"C" or "Chair"
"D" or "Desk"
or even:
"Chair (C)"
and
"Chair (D)"
My post presumes "C" or "D"
0

LVL 74

Assisted Solution

Jeffrey Coachman earned 400 total points
ID: 40577727
I think Jim's Post would works better for you.
My post presumes that you have no other filters in the Report's Recordsource (and was untested)
0

Author Closing Comment

ID: 40577757
Thank you!!!
0

LVL 74

Expert Comment

ID: 40577774
If Jim's post worked for you, ...you did not have to accept my post as assisting.
Jeff
0

## Featured Post

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
###### Suggested Courses
Course of the Month19 days, 11 hours left to enroll