Solved

Count on group footer

Posted on 2015-01-29
7
93 Views
Last Modified: 2015-01-29
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
Comment
Question by:urjudo
  • 4
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
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 57

Accepted Solution

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

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

and:

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

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

Jim.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
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

by:Jeffrey Coachman
Jeffrey Coachman earned 100 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

by:urjudo
ID: 40577757
Thank you!!!
0
 
LVL 74

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now