Total max of a line

[robbydogg]

Hello,
I am running a report which has the following:
Report & Page header: contents - fixed
UPC header: contents depending on parent company
Retailer Header: Retailer name & reference number, dependant on UPC
Detail: containing measures by month, a total, plus an extra field showing submissions
Retailer Footer: to show totals for that specify retailer
Page Footer: date and page


In the detail there is a column, which has the same number in it for the retailer next to the different measures, but varies by retailer.
Eg:

retailer
645
measure jan feb apr may special

measure(a) 1 2 2 2 1
measure(b) 2 2 3 1 1

retailer
620
measure jan feb apr may special

measure(a) 2 2 2 2 6
measure(b) 3 2 5 5 6


what i need to do is get the max value of the special column by retailer, totalled up in the report footer.

can you help??

thanks
rob

 

[Kafrin]

I think that if you put a text box with ControlSource:
= Max([Special])
in either your Retailer Header or Retailer Footer and name it something like MaxOfSpecial, then you can sum this box in the Report Footer by having a text box with ControlSource:
= Sum(MaxOfSpecial)

The Max box can be hidden (ie set Visible property to No).

It can be tricky in report summing calculated controls, but I think that for this case it should work.

 

[robbydogg]

hi,
thanks for the reply - i have tried this method, but for some reason it doesn;t allow it to work. it keeps asking for an entre for the "MaxofSpecial" box. i get the feeling it's not recognising it but can't suss out why?

 

[Kafrin]

Shoot, normally Access plays nicely with calculated controls on reports when there's no other easy way to do it.

There are then two options I can think of.

1) Bring Max(Special) into the query behind the report somehow. This may involve a new query that groups on the primary key of Retailers and has max(Special). This can then be linked in your report query on Retailer pk.

2) Write a VBA function that you call in your report footer that returns the number you want. This would probably take the form of opening a recordset based on a query like the one in (1), and looping through it to sum the max(Special) field.

 

[robbydogg]

hi,

i managed to get around the problem by using a sub-report.
i had the user enter the required details in the front form, which held the certain criteria for the search. The main form displays the detail and the sub form runs off a separate query, using the same data, but summing up the totals for the whole report. et voilá

thanks
rob