Sum only some records 2

[Pukeko]

I have a form that I enter things like name and date. On the form I also have a number field (intNum1) along with a logical field next to it. I use the logical field to indicate if I want the number to be included in the sum on my report. I want the entry listed even if the number is not included in the total.

I have the report laid out as I want, with all entries list by name and in date order. My problem is how do you only add up some of the records. I can sum all of them using Sum(intNum1) however I need to add a 'IF' clause - IF logcal1 = yes then add intNum1.

If anyone can help me it would certainly be of assistance, either by showing me the expression to use or suggesting another way to do this.

Thanks
Pukeko

"The Pukeko is a bold and fearless bird. It has learned that trains and motorcars are harmless and takes no notice of them." - WRB Oliver.


Thanks

 

[MLZ]

Hi there,

Please try this:

1. Add a text box field in your report layout. If you want the sum to appear for a group, then add the text box in that group's footer section. If you need to show the sum at end of the report, i.e. grand total,then add the text field in the report footer section.
2. Right click and choose properties.
3. Click the tab labeled "Data".
4. Set the Control source to
=iif([logcal]="yes",[intNum1],0)
5. Set Running sum field to "Over Group" .
6. Make sure to set the Visible property to Yes.

Hope this helps.

Good luck,
mlz

 

[Pukeko]

Thanks MLZ,

I tried your suggestion, but how does it do the summing over multiple records, checking 'logical' each time?

I tried changing "[intNum1]" to "Sum([intNum1]) put it only seems to check logical on the first record for name and then biased on that either add or don't add all the records.

That is, if the Logical=Yes on first record for name then it adds all intNum1's up, regardless of the value of Logical on the remainder of the records.

Any other suggestions would be appreciated. Some ideas - Can you run an SQL query from the expression builder? If so as the DB gets bigger, is this going to add to processing? As it appears to be processing the math after all the records have been processed (i.e it is in the footer of the section, should I/can it be done, be doing a ”If logical=yes, a=a+intNum1” expression within the detail section (i.e. as it processes each record) and then in the footer section refer to ‘a’ which would be the total?

Thanks again


Pukeko

"The Pukeko is a bold and fearless bird." - WRB Oliver.

 

[dhookom]

All you need is a text box in the group or report footer with a control source of:
=Sum(Abs([logcal1])*[intNum1])


Duane
MS Access MVP

 

[Pukeko]

Thanks dhookom.

It worked perfectly, and even more I understand the workings behind your suggestion.

Thanks again for a simple and elegant solution.



Pukeko

"The Pukeko is a bold and fearless bird." - WRB Oliver.