Calculating Sums from a Combo Box or Check Box

[dtecta]

I have a "Status" combo box in my Query and Form that has three options: "Withdraw," "Needs to Make Up," and "No Show." I would like to be able to calculate a separate sum for each of those options. For example, if I have 2 students in the class whose Status is listed as "Withdraw," I would like the report to show a total number of students who withdrew (Withdrawls=2) in the Footer. I know I could do a grand total of all students by using a control of =Count([ControlName]), but I want to be able to specify subcalculations within that control. If I can't do this with a combo box, can I do it with checkboxes and specify which boxes were checked?

 

[CosmoKramer]

In the report footer put a textbox whose control source is something like this:

=DCount("[status]","yourQueryName","[status] = 'Withdraw'")

Put one for each status and you should be all set....

 

[dtecta]

Thank you! It works perfectly when I only have one class per report. What happens if I want to have more than one class on a report and I have the report sorted and grouped by ClassID? How do I get the control to give me the status of students in each class?

 

[CosmoKramer]

Do you mean the count of students in each class?

 

[dtecta]

Yes, the count of each type of status in each separate class.

 

[CosmoKramer]

Add 3 invisible text boxes to the detail section. Define the control source for each to something like this for each of the possible status types:

=IIf([status]="Withdraw",1,0)

For all 3 text boxes, set the Running Sum property to Over Group. This will cause the last instance of these text boxes to contain the count for each status for that group.

In your group footer place 3 text boxes to display the counts. Set their control source to the Name of its corresponding invisible text box.

Let me know if this helps...

 

[dtecta]

If it weren't for you, I would NEVER have figured this out. Actually, the only way I could get it to work was to put those Iff statement in the underlying query. Without doing that, the report wouldn't let me sum the text boxes in the footer because it saw those field names as parameters - I think I tried every combination of parentheses. Anyway, the report does exactly what I want it to now. Thank you.

p.s. Love your username - I'm a fan.

 

[CosmoKramer]

You're welcome....I couldn't make a living off of Movie Phone forever....