Need help writing an Access VBA query to count ORDERS (not transactions)
Posted on 2014-07-28
I have a table called InvoiceHistory
with fields SearchNumber,price,quantity,shippeddate
Customer returns are included in the table with a negative quantity
SearchNumber Price Quantity Amount ShippedDate
006207341914000 $0.65 100 65 5/19/2010
006207341914000 $0.50 815 407.5 3/14/2012
006207341914000 $0.50 -813 -406.5 3/26/2012
006207341914000 $0.50 600 300 4/30/2012
I need a query that contains the following columns:
My summation query for this part shows:
While Quantity and Amount do reflect the true QTY and $$ received,
The NumberOfSales is very misleading because it counts transactions.
This item, actually has four transactions.
We sold it three times, but one customer returned it.
So we actually only have 2 sales.
How can I get 2 to show up in my query?
To make things more complicated, the customer who bought 815 only returned 813.
Any suggestions how to handle this?
We are writing this report to help make decisions about dead inventory.
A part that we have sold five times and has had four returns shows up with 9 transaction, which is misleading.
SELECT qryOrdersThatMeetMOQ.SearchNumber, Sum(qryOrdersThatMeetMOQ.Quantity) AS PartsSold, Sum([quantity]*[Price]) AS SalesTotal, [SalesTotal]/[PartsSold] AS AverageUnitPrice, Count(qryOrdersThatMeetMOQ.InvoiceNumber) AS NumberOfSales
GROUP BY qryOrdersThatMeetMOQ.SearchNumber
HAVING (((qryOrdersThatMeetMOQ.SearchNumber) Is Not Null And (qryOrdersThatMeetMOQ.SearchNumber)<>""));