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));>

Thank you for your assistance.
Mike
MikeOsborneAsked:
Who is Participating?
 
peter57rConnect With a Mentor 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
 
MikeOsborneAuthor 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
 
peter57rCommented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
MikeOsborneAuthor Commented:
I will try your suggestion and advise. If needed I will include detailed information.
0
 
MikeOsborneAuthor Commented:
Thanks,
I was able to get it to work.
Mike
0
 
MikeOsborneAuthor 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
 
peter57rCommented:
I think you have accepted the wrong answer
0
 
MikeOsborneAuthor Commented:
Works great
0
 
MikeOsborneAuthor 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.

All Courses

From novice to tech pro — start learning today.