H
HartJF
My database has a table with 400K college recruitment records for a
three-campus school. One field is a four-character term code "yytc", where
yy is the year (the two-digit protocol was established before anyone was
concerned with Y2K), t is the term (we have up to 4 entry points per year),
and c is the campus (e.g., 062D, our second term this year for campus D).
I am analyzing our recruitment efforts over the last twelve years. I have a
query that accumulates data from the large table by partitioned time of
inquiry before the term begins (1-3 months, 4-6, etc.). I need to report
the data by partition for each term, aggregate it by partition for the t-th
term of the year across all years for that campus, aggregate it by partition
for the campus, and aggregate it by partition overall. The report will look
like:
Campus C (right$(strTermCode,1) header)
First Terms (mid$(strTermCode,3,1) header)
Term 061C (strTermCode header)
1-3 mos. nn nn nn
4-6 mos. nn nn nn
Term 061C Total nn nn nn
(strTermCode footer)
Term 051C
1-3 mos.
4-6 mos.
Term 041C
...
First Terms Total (i.e. aggregate for "??tc", viz. "??1C" --
mid$(strTermCode,3,1) footer, which contains a subreport)
1-3 mos.
4-6 mos.
...
Second Terms
Term 052C
...
Campus C Total (i.e. aggregate for "???c", viz. "???C" --
right$(strTermCode,1 footer, which contains a subreport)
1-3 mos.
4-6 mos.
...
College-Wide Total (report footer)
1-3 mos.
4-6 mos.
This would be a no-brainer if I just had to total the data, but I have to
partition the total.
My problem: I don't grasp how to communicate the aggregative parameters
from the main report to the subreports. Can I use child/parent linking with
an expression, or do I create an event procedure for the footer's OnFormat
event to use the OpenArgs parameter in a .OpenReport statement, do I use
brute force with DSum functions referring to a parent control? Or do I need
a completely different strategy? (Unfortunately, I don't have the choice to
forget the project
Thanks for your help, folks!
three-campus school. One field is a four-character term code "yytc", where
yy is the year (the two-digit protocol was established before anyone was
concerned with Y2K), t is the term (we have up to 4 entry points per year),
and c is the campus (e.g., 062D, our second term this year for campus D).
I am analyzing our recruitment efforts over the last twelve years. I have a
query that accumulates data from the large table by partitioned time of
inquiry before the term begins (1-3 months, 4-6, etc.). I need to report
the data by partition for each term, aggregate it by partition for the t-th
term of the year across all years for that campus, aggregate it by partition
for the campus, and aggregate it by partition overall. The report will look
like:
Campus C (right$(strTermCode,1) header)
First Terms (mid$(strTermCode,3,1) header)
Term 061C (strTermCode header)
1-3 mos. nn nn nn
4-6 mos. nn nn nn
Term 061C Total nn nn nn
(strTermCode footer)
Term 051C
1-3 mos.
4-6 mos.
Term 041C
...
First Terms Total (i.e. aggregate for "??tc", viz. "??1C" --
mid$(strTermCode,3,1) footer, which contains a subreport)
1-3 mos.
4-6 mos.
...
Second Terms
Term 052C
...
Campus C Total (i.e. aggregate for "???c", viz. "???C" --
right$(strTermCode,1 footer, which contains a subreport)
1-3 mos.
4-6 mos.
...
College-Wide Total (report footer)
1-3 mos.
4-6 mos.
This would be a no-brainer if I just had to total the data, but I have to
partition the total.
My problem: I don't grasp how to communicate the aggregative parameters
from the main report to the subreports. Can I use child/parent linking with
an expression, or do I create an event procedure for the footer's OnFormat
event to use the OpenArgs parameter in a .OpenReport statement, do I use
brute force with DSum functions referring to a parent control? Or do I need
a completely different strategy? (Unfortunately, I don't have the choice to
forget the project
Thanks for your help, folks!