Calculations within a report

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

I have a database where I keep track of invoices. Some of the fields are:
Vendor, Invoice #, Invoice Date, Service Period, Service Period End, and
Amount. The Service Period is entered as 1/02/2008 (for example).

I have created a report which includes all of this information. The report
is ran off of a query in which the criteria is set to show all of the
invoices for the previous quarter. When using the Report Wizard, I told it to
group by Vendor, then by Service Period per Month. (This created a new field
called “Service Period per Month.â€) When the report is ran, any dates that
show January (for example) as the month are under the “January 2008†heading.


The total invoice amount is being calculated (no problem there). Where I
run into a problem is when I try to calculate the average amount per service
period. Ideally, each vendor would have four service periods, but this is
not always the case. Some have only one or two, maybe three, could be all
four. This is why I’m having trouble.

I would be grateful if anyone out there could help. (If you need further
clarification or if it can’t be done, please let me know.)
 
Jen said:
I have a database where I keep track of invoices. Some of the fields are:
Vendor, Invoice #, Invoice Date, Service Period, Service Period End, and
Amount. The Service Period is entered as 1/02/2008 (for example).

I have created a report which includes all of this information. The report
is ran off of a query in which the criteria is set to show all of the
invoices for the previous quarter. When using the Report Wizard, I told it to
group by Vendor, then by Service Period per Month. (This created a new field
called "Service Period per Month.") When the report is ran, any dates that
show January (for example) as the month are under the "January 2008" heading.


The total invoice amount is being calculated (no problem there). Where I
run into a problem is when I try to calculate the average amount per service
period. Ideally, each vendor would have four service periods, but this is
not always the case. Some have only one or two, maybe three, could be all
four. This is why I'm having trouble.

I would be grateful if anyone out there could help. (If you need further
clarification or if it can't be done, please let me know.)
Click to expand...

I hope I've understood this Jen, I can't quite visualise what you are
saying.

You could try this. In the section you want to count, put a text box called
txtRun. In it, type =1
Set its Running Sum property to Over Group and to get your average have
=Sum([Amount])/[txtRun]
(it only looks at the last Value of txtRun)
If this doesn't get the result you want, then set the Running Sum to Over
All. Make it visible (and red) at first so you can see what it is doing and
if you have it in the correct Section then set the Visible Property to No
when it does what you want.

Evi
 
Back
Top