show difference in group footer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that shows two year's data, and I want to calculate the
change in the group footer. The group is by product, then there are two
detail records for each product, one for last year and one for this year, and
I want the group footer to show the change. I'm not sure how to do this.
 
trillium97 said:
I have a report that shows two year's data, and I want to calculate the
change in the group footer. The group is by product, then there are two
detail records for each product, one for last year and one for this year, and
I want the group footer to show the change.
Click to expand...


Generally, you should put together a query that gathers the
related data in a single record. This can usually be done
by Joining the table to itself:

SELECT T.product,
T.year As CurrentYear
T.amount As CurrentAmount,
P.Year As PreviouYear,
P.amount As PreviousAmount,
. . .
FROM table As T INNER JOIN table As P
ON T.product = P.product
AND T.year = P.Year + 1
WHERE T.year = someyear

With that as the report's record source query, you can lay
out the detail section for both year's data including the
difference. There's no need to group on product since each
product will only have a single record.

The only other way I can think of to do what you want
strikes me as kind of hokey. Put an amount text box in the
group header to get the first detail's amount. The group
footer can have another amount text box to get the value of
the second year and you can go from there.
 
Back
Top