# Average use of item by customer

Hello experts. I searched the listings but could not find an answer for my question. I have an inventory database that "sells" products to "customers". I need to generate a report that will group the products by name and then list the customers and display each transaction and the total sold. The total number of products are then summed for each customer. So far this is easy. What I need to show is the average amount sold to each customer. Thus the problem is the average is taken fron the grand total and not the sum of a particular customer.
EXAMPLE: widget type 1 is sold to John on seven different transactions for a total amount of 50 widgets. The total number of widget type 1 sold is 200, what is Johns percentage? It appears that the problem I have is the average is being calculated on the total number of widet type 1 sold to all customers and not the sub total for each customer.
The final query has user selected variables but this is the basic query.....The calculations have been attempted on the report, not in the query.
<SELECT [Inventory Transactions].TranType, [Inventory Transactions].CustomerID, [Inventory Transactions].ProductID, [Inventory Transactions].UnitsSold, [Inventory Transactions].TransactionDate, Customers.CustomerName, Products.ProductDescription, Products.UnitPrice, Products.ProductName FROM Products INNER JOIN (Customers RIGHT JOIN [Inventory Transactions] ON Customers.CustomerID = [Inventory Transactions].CustomerID) ON Products.ProductID = [Inventory Transactions].ProductID WHERE ((([Inventory Transactions].TranType)="i") AND (([Inventory Transactions].ProductID)=10022));>

Mike
###### Who is Participating?

Commented:
You can get a percentage for each customer total by putting a textbox into the customer group footer with the controlsource of

=Sum(yourquantityfield)*100/txtGrandTotal

where txtGrandTotal must be the name of the textbox that holds the sum of the quantity for the whole report.
0

Author Commented:
Thanks, I'll give that a try. Several items are listed in the report having the same field for the sum. Will placing the text box in the correct footer calculate properly?

Thank you
0

Commented:
Sorry but I don't understand what you mean by...
"Several items are listed in the report having the same field for the sum".
0

Author Commented:
I will try your suggestion and advise. If needed I will include detailed information.
0

Author Commented:
Thanks,
I was able to get it to work.
Mike
0

Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for MikeOsborne's comment #a38403345

for the following reason:

Works great.
0

Commented:
I think you have accepted the wrong answer
0

Author Commented:
Works great
0

Author Commented:
Sorry, wrong box selected. I have accepted the original answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.