Wrox Programmer Forums Total Count of Specific Records in a Report
 Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
 Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersâ€™ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
July 24th, 2007, 02:42 AM
 Authorized User Join Date: Mar 2006 Location: , , . Posts: 83 Thanks: 1 Thanked 0 Times in 0 Posts
Total Count of Specific Records in a Report

Hiya folks!

I am sure this is a simple one, but I just cant seem to figure it out:

I have a report that shows something similar to the following:

PRODUCT CATEGORY
Hamburger Meat
Trout Fish
Beef Steak Meat
Custard Dessert
Lamb Cutlets Meat

Now what I cant seem to do is to have the report show the following summary for the above:

Number of Meat dishes: 3
Number of Fish dishes: 1
Number of Dessert dishes: 2

I have tried various versions of count and dcount, but cant seem to get it to work.

Thank you!
July 24th, 2007, 06:51 AM
Friend of Wrox
 Points: 9,611, Level: 42
 Activity: 0%

Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Create your text boxes for each ingredient, then add this sort of thing:

=Sum(IIf([Category]="Meat",1,0))

This says, if the category value is "meat" then add one to the counter, otherwise add 0.

Also,

=Sum(IIf([Category]="Fish",1,0))
=Sum(IIf([Category]="Dessert",1,0))

etc.

Did that help?

mmcdonal
July 24th, 2007, 07:19 AM
 Authorized User Join Date: Mar 2006 Location: , , . Posts: 83 Thanks: 1 Thanked 0 Times in 0 Posts

I tried it, but it is not working.

I forgot to mention that the [Category] field is a calculated value. It uses the If Then function:

If Field1="Low" and Field2="Low"
Then Category value should be "Negative"
Otherwise
Category value should be "Positive"

So the summary would show how many positives and negatives results there are in the report.

Thanks!
July 28th, 2007, 07:12 AM
 Authorized User Join Date: May 2006 Location: , , India. Posts: 47 Thanks: 0 Thanked 0 Times in 0 Posts

You can also do it in other way..

Create simple query and make a report. Once you made the report, goto design mode of report and created header over Category field n set the property as below:
Group header = Yes, Group Footer = No, Group on = Each Value, Group Interval = 1 and Keep Together = No.

The place one unbound textbox in detail section (near to PRODUCT text box) and put in there =1 as source.

This way you will get counter that counts how many item of Meat and Fish bla bla..

Hope this helps. If not, send me your email id I will send you a sample.
Ashfaque_Online@yahoo.com
July 30th, 2007, 09:52 AM
 Authorized User Join Date: Mar 2006 Location: , , . Posts: 83 Thanks: 1 Thanked 0 Times in 0 Posts

This worked beautifully, Ashfaque.

I went further one step:

I named the unbound text box that you mentioned in your post "txtCounter". Then I added a group footer for Categories, and added an unbound textbox. The control source for this new unbound textbox is =[txtCounter]. The result when I go to "Report View" always shows me the count in that category. Perfect!

The problem I have now is how can I use this result in a formula that exists in another field? This is the main reason I need the count.

Thank you!
August 1st, 2007, 03:50 AM
 Authorized User Join Date: May 2006 Location: , , India. Posts: 47 Thanks: 0 Thanked 0 Times in 0 Posts

Ok
If you did the same what I told you then do the following.
1. in the Category header of report place 2 unbound textboxes (let us say Text0 & Text1) then place this =Sum(IIf([Category]="Meat",1,0)) in text0 as datasource. ok?

2. Now, place this =IIf([Text0]>0,"Number of Meat dishes :" & [text0],"") into text1 text box

3. Run the report and it would give you desired result.

4. This process was only for "Meat" so you would need to do the same for other catergories.

5. Set conditional formatting on Text0 (If field value is "Eqaual to" 0 then make both text and background White ..I hope you know this how to do it)

6. To appear all them in one line, place all your 3 categories (text0 and other) ONE-UPON-ONE so once there is 0 value it would be disappeard.

Hope this would meet your requirement. If not, I will send you db I created for you as sample.

Good luck.

With kind regards,
Ashfaque

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is OffTrackbacks are Off Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post Count, sum, count a value, return records CongoGrey Access 1 April 18th, 2005 02:25 PM count the TOTAL number of segments crmpicco Classic ASP Basics 2 February 1st, 2005 05:03 AM Getting Total Page Count eman2a Crystal Reports 0 August 11th, 2004 10:53 AM Sub Total, Count discriminating dkaco BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 0 July 23rd, 2004 12:04 PM Total Page Count denis74hd Crystal Reports 4 May 27th, 2004 02:31 AM

All times are GMT -4. The time now is 05:00 AM.