Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 24th, 2007, 02:42 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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
Fruit Salad 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!
Reply With Quote
  #2 (permalink)  
Old July 24th, 2007, 06:51 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old July 24th, 2007, 07:19 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thank you for your reply!

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!
Reply With Quote
  #4 (permalink)  
Old July 28th, 2007, 07:12 AM
Authorized User
 
Join Date: May 2006
Location: , , India.
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

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
Reply With Quote
  #5 (permalink)  
Old July 30th, 2007, 09:52 AM
Authorized User
 
Join Date: Mar 2006
Location: , , .
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Thank you so much for your reply!

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!
Reply With Quote
  #6 (permalink)  
Old August 1st, 2007, 03:50 AM
Authorized User
 
Join Date: May 2006
Location: , , India.
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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 Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.