?
Solved

Count on group footer

Posted on 2015-01-29
7
Medium Priority
?
102 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
[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
  • 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 58

Accepted Solution

by:
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
ID: 40577717
Count(<expression>) just tally's up the count of records where a field (or fields) is not null.

Jim.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
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.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

649 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