[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Average use of item by customer

Posted on 2012-09-13
9
Medium Priority
?
425 Views
Last Modified: 2012-09-16
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
0
Comment
Question by:MikeOsborne
  • 6
  • 3
9 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 38397875
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

by:MikeOsborne
ID: 38399458
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

by:peter57r
ID: 38399472
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:MikeOsborne
ID: 38399564
I will try your suggestion and advise. If needed I will include detailed information.
0
 

Author Comment

by:MikeOsborne
ID: 38403345
Thanks,
I was able to get it to work.
Mike
0
 

Author Comment

by:MikeOsborne
ID: 38403360
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

by:peter57r
ID: 38403361
I think you have accepted the wrong answer
0
 

Author Closing Comment

by:MikeOsborne
ID: 38403553
Works great
0
 

Author Comment

by:MikeOsborne
ID: 38403554
Sorry, wrong box selected. I have accepted the original answer.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question