Count & Sum on Report

[OceanBlue]

I have a report that is generated from a query. The database houses information of donations collected from year to year. The report displays information such as the First Name, Last Name, Company, Date, Amount of Donation.

At the end of the report I would like to display the Total amount of donations that were received(SUM). Also, I wouldl like to display the total number of donations that were made (COUNT).

How would I go about doing that in the report? I would need to use the Amount of Donation field in order to get the SUM and COUNT... by how do I do that to reflect in the Report?

Any help will be greatly appreciated.

 

[THWatson]

OceanBlue
You might try...

1. In the report footer, put an unbound text box. For its control source, use
=Sum([Donation])
This will get the sum of the donations.

2. In the report footer, put a second unbound text box. For its control source, use
=Count([Donation])
This will get the count of the donations made.

A caveat...
If there might be a listing for a person or company without a donation, then for the count total try
=Sum(Abs([Donation]))

Tom

 

[THWatson]

OceanBlue
Sorry. Typed in haste.

The caveat is supposed to be for the Sum of the donations, not the Count.

What you are really doing with the Count expression is counting the number of occurrences of a particular data field. So you could use any field that occurs every time. In other words...
=Count([WhateverFieldYouWishToCountBy])
Tom

 

[OceanBlue]

Tom,

I tried
=Sum([Donation])

=Count([Donation])

But I get an error when I generate the report..where that number is supposed to be it says #Error

 

[THWatson]

OceanBlue
Two things...

1. Make sure that the text boxes are in the Report Footer or they won't work.

2. I used Donation as an example. Change that to whatever is the exact name of your Donation field. If the field is called "Amount of Donation" then change the expressions accordingly.
For example...
=Sum([Amount of Donation])

Tom

 

[OceanBlue]

Thanks,

It worked I was putting it in the Page Footer by mistake. Thanks so much.

 

[OceanBlue]

Another question that I have is:

I have created a query that calcuates totals for each product. I have attached a screen shot of the query.

I will be creating a report for each of the products with detailed information.

As the footer I want it to be the sum and count obtained from the totals query. But if i insert the query as a subreport in the footer it will list the totals for all of the products. I only want to include the totals for one product.

How can I go about doing that. I tried to change the rowsource but that didn't work.

 

[THWatson]

OceanBlue
Not 100% clear what you are doing.

Are you
1. running a report for only one product at a time
or 2. running a report for all products but want a total and count by product?
or 3. running a report for all products but want only a total and count by one specific product

Which is it: 1, 2 or 3

Tom

 

[OceanBlue]

Tom,

It is # 1

I am running a report for only one product at a time

But I like #2 you listed, running a report for all products and having a total and count by product.

If it's not too much trouble could you explain how I can do #1 and #2?

Thank you, your help is greatly appreciated.

 

[THWatson]

OceanBlue
1. If you are creating a report for one product at a time, then you need a way to select the product for which you are running the report. There are a number of ways to do that. The best way is to create an unbound form in which you select the product. You can select the product either by typing in a text box or by having a list box that lists the products.
Then you reference the control from the unbound form in your query. In the Product column in your query, add the criteria Forms!YourUnboundFormName!ControlName
The ControlName would be either the name of your text box or your combo box.

Then in the report, since you only have the one product you want, you put the Sum and Count text boxes in your Report Footer, same as we described above.

2. If you are doing a report for all products, then do a Sort and Group by product, and make sure you have a Group Footer. You could put a Sum and Count for each product in the Group Footer.
Then in the Report Footer put text boxes that do a total of the Sum and Count text boxes from the Group footers.

For example, let's say that one of the products is Apples and a second is Bananas, and you have sorted and grouped by products, each group having a Group Footer. In the Group footer, put a text box with the expression =Sum([Product]).
Do the same for Count, and then repeat the process in the Report Footer.

Tom