Summing unbound calculated controls at grouping levels

[Guest]

I see many posts have been made on this, and the advice is always to repeat
the calculated control formula itself in the group level's summing control.
Quite valid if its just simply totaling all the records.

I am setting up a database for a loyalty program in my shop. Each fortnight
is a separate period in which to qualify purchases must exceed a certain
figure. The threshhold for singles is lower than for a family.

As a result, the fortnightly subtotal (a calculated control) is a 3 line
test of nested "Iif"s. It works well so far (calculating a percentage of
total purchases for that fortnight only where the purchases exceed the
relevant fortnightly threshhold), but now I need to calculate the grand total
of each member's bonuses for the year. Reapplying the same formula at the
customer grouping level would treat the whole year as a single period instead
of properly calculating by the fortnight.

My original instinct was to set up a Query that sub-totals each fortnight
and base the report on that. However, searching "Help" and looking at
various examples I couldn't find any indication that you can sub-total
queries into grouping levels and figured that reports were intended for that
function.

Ideally, the ability to build a query (update?) that would calculate each
fortnight's bonuses and enter them into a separate table would be the go. I
wonder if that's feasible..... Any thoughts? Oh, I'm using Access 2000 on Win
XP.

 

[Jeff Boyce]

One approach to this would be to not restrict yourself to a single query (or
a single report). If you built queries that performed your subtotaling, and
based reports on those, you could have one main report and embed the
subreports, linked by whatever identifier (?familyID) you are using.

 

[Guest]

Thanks, Jeff.
I had arrived at the conclusion that thats what I will need to do, but I'm
still trying to get my head around these totaling queries. Tried one and no
records, nothing. Don't know what I'm doing wrong. I have a feeling that a
query showing each relevant record and a neat row of totals along the bottom
line isn't the way these things are designed to work. I guiess I'll get there
(normally do eventually after much wasted time and frustration).
Regards,
Keith

 

[Jeff Boyce]

On the contrary, if you include details in the details section of the
report, you can add unbound controls at the bottom of the GroupBy section
that surrounds the detail section. These controls will have, as a source,
something like:

=Sum([YourDetailField])

If you are trying to do subtotals by one group level, and "grand totals" by
a higher-level Group, you can do this, too. Again, in either the footer (or
the header) of the GroupBy, use a control source like that shown above, to
sum across all detail records in the group.

Sorry if I misunderstood what you were trying to accomplish in that first
round of posts...