Hi, The following code appear...

Hi,

The following code appears in a undefined field on the record band of my report.

Sum([New.Number]*[New.Cost])

I wish to get the AVG of those calculations based on a Group Band.

How do I achieve this ?

Many thanks,

Paul

You should be able to copy this field to the group band and change the defination to:

Sum([New.Number]*[New.Cost])/count([New.Number])

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

Thanks for your help Dan but the code I gave you totals ALL the recods, for example :- Rec1   (Number) 2  (Cost) 3

                 Rec2  (number) 1   (Cost) 1     TOTAL = 7

Now I want to get the AVG of these TOTAL Costs based on a Group Band.

Thanks for your interest to help.

Rds,

Paul

Let me see if I understatnd your situation correctly.

Your report consists of a records grouped by some criteria.  You are calculating a sum of the records within the group but want to calculate the average of the calculated sums between the groups.

In this case the Average is going to be placed outside the group band and should look something like

Sum([New.Number]*[New.Cost])/count(your group criteria)

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

New.db Has the following fields :-Paddock,Crop,Date,Number,Cost. The first Group Band is based on Crop.  The second Group Band is based on Paddock The code Sum([New.Number]*[New.Cost]) tells me the total for each Paddock.  I am now wanting the AVG Total per Crop.

Please dont leave me in suspenders !

I gather that the code Sum([New.Number]*[New.Cost])  is located outside the Crop group band and inside the Paddock group band.  If you copy this field and past it inside the Crop group band you will get a total for each crop within each paddock.  Then you can modify the code to Sum([New.Number]*[New.Cost]) /count(Date) to get an average.  I used date since this is most likely a unique value.  You man need to add a unique identifier to get an accurate count.  

If you are attempting to get the average of the crops you are going to need to take another approach.  The report you have described is sorting the data by paddock and then crops within the paddocks.  Getting the average by the Crops alone would require a different sorting possibly a seperate report altogether.  An alternative would be to run queries to get the summary results and then create a report based on the generated data.

Dan Fought
Senior Programmer Analyst
Prestwood IT Solutions
http://www.prestwood.com

I have altered the query to call up results based on a crop, eg Wheat.

The following code  :- Sum([SprayALL.Rate]*[SprayALL.UnitPrice])/UniqueCount([SprayALL.Paddock#]) -placed between the Group Band on CROP and PAGE correctly averages the totals ON EACH PAGE, but fails to avereage all totals on all pages when, placed between PAGE and REPORT.

Thanks for hanging in there for me !

Have just cracked it, -used an exclusive field in the table to use for the UniqueCount.

Thanks once again.