Solved

Count on group footer

Posted on 2015-01-29
7
95 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

776 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