Counting items in a report

[prn]

Hi folks,

I've got another (inherited) puzzle that I don't understand.

A report that I need to modify contains a subreport that lists a variable number of items in its detail section and then has a footer which contains a count of those items. That is, the "detail" section of the main report consists of a page for each sub-category listing the items in that category and then includes a count of the items for that sub-category.

The way whoever (i.e., whichever of the half-dozen people it was) did that was to include with each detail item, an invisible textbox field (named COUNT with a "Control source" of "=1".

The puzzling part of this is that when I made the textbox visible, I found that the value in the boxes increased, so that rather than simply having a value of "1" in each line, the successive COUNT boxes were, indeed, a count. My anonymous predecessor then inserted the count into the footer as "=Sum([Count])/15" which, for reasons I fail to appreciate, actually appeared to work.

Unfortunately, I have to say "appeared to work" rather than "worked" because although it gave the correct total most of the time it occasionally geve the wrong total. I'm not sure why that is the case -- possibly a rounding error, but some of the totals are wrong. Frankly, I'm not even sure why most of them are right. What dividing by 15 should have to do with it makes no sense to me.

Anyway, my first real question is why "=1" as the control source for a textbox results in anything other than "1".

My next question is how to extract a useful value from the boxes. I have tried "=Max(Count)" but what I found is that the successive instances of this subreport all give the same total, whether or not that is the correct number for that page, e.g. all of them show "14" whether the real count for that page should be 14, 3, 22, or whatever. Using "=Last(COUNT)" does exactly the same, giving the same weird number that is the same for all of the pages. Using "=COUNT" is almost the same in that almost all of the pages get the same number as each other (10, which is not 14 and so is different from Max or Last), but a few pages get a different number (20). What I want to do is either to pull the last value of COUNT into the total box or else make all of the boxes have a value of "1" and then use "=Sum(COUNT)".

Any clues about what is going on, and/or what I am doing wrong would be gratefully appreciated.

Thanks,
Paul

[Stewart Ross]

Hi Paul. You have my sympathies here; I'd scrap the current approach, as the way this has been implemented so far is in my opinion too flawed to rescue.

The fundamental flaw is to try to add counts etc after the event using unbound textboxes - this is likely to rely on code behind the report's sections to set the textbox values. Such an approach is at best difficult to maintain; at worst it may not give accurate results at all. Since the actual values of the textboxes do not appear to match the control sources something must have changed them - I can only guess at this, but presumably as a result of on-format or on-print event code behind one or more of the report sections.

Unless code behind sections is carefully written it can result in errors as the sections are revisited during formatting etc, leading to repeated updates of the same value.

I would suggest that the simplest way to deal with this is not to waste time trying to maintain the unmaintainable. Instead, add computed fields to the query on which the subreport is based, so that something countable is available for each row of the base query. With such a computed field you can then use the normal report summing and counting to summarise the data without resorting to bespoke programming or using unbound textboxes.

A computed field for the base query could be based on a simple IIF, like this:

ThisIsAWidget: IIF([whatever makes it a widget] = [somecondition], 1, 0)

Then in the subreport you can use the normal sum and count facilities to summarise the values in the ThisIsAWidget fields and any others that you add.

Hope you resolve this one satisfactorily...

-Stewart

[prn]

Thanks, Stewart.

I did wind up adding a field to the base query, but I just added a line to the SELECT:

1 as TICK

and then summed the ticks. It works essentially like the original should have worked, but in this case, the TICKs are all 1 instead of an increasing list and now the SUM actually works.

I still do not understand why an unbound control with a value of "=1" should have any value other than "1" but it did, so I solved it in the SQL and left myself with minimal rewriting.

Thanks,
Paul

[Stewart Ross]

Hi Paul. I think I may have a possible answer to the mystery of the incremented values - in a report you can set a control's Running Sum property to Over Group or Over All. Bet this being set was the reason for the auto increases on the unbound control. I have tried it out now myself and it increments the unbound control as you described.

Glad you found a suitable resolution which is far more transparent and very simple.

-Stewart

[prn]

Thank You, Stewart!

Yes! I had not noticed the Running Sum property and had not been aware of it at all, but you're right. It was being incremented "Over Group" Whoever wrote this report obviously didn't understand it, either and then tried to work around it with quite buggy results. If that had been set to "No" the report would have been so much simpler.

I still don't understand how whoever it was got where they did, but at least one of the small mysteries is now cleared up.

Thanks a lot!

Paul