Solved

# Average use of item by customer

Posted on 2012-09-13
420 Views
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
0
Question by:MikeOsborne

LVL 77

Accepted Solution

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 Comment

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

LVL 77

Expert Comment

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 Comment

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

Author Comment

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

Author Comment

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

LVL 77

Expert Comment

I think you have accepted the wrong answer
0

Author Closing Comment

Works great
0

Author Comment

Sorry, wrong box selected. I have accepted the original answer.
0

## Featured Post

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…