p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access (http://p2p.wrox.com/forumdisplay.php?f=18)
-   -   Total Count of Specific Records in a Report (http://p2p.wrox.com/showthread.php?t=60416)

Odeh Naber July 24th, 2007 02:42 AM

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!

mmcdonal July 24th, 2007 06:51 AM

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

Odeh Naber July 24th, 2007 07:19 AM

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!

Ashfaque July 28th, 2007 07:12 AM

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

Odeh Naber July 30th, 2007 09:52 AM

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!

Ashfaque August 1st, 2007 03:50 AM

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



All times are GMT -4. The time now is 04:24 AM.

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