Average use of item by customer
Posted on 2012-09-13
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.