Need help writing an Access VBA query to count ORDERS (not transactions)

Hello!

I have a table called InvoiceHistory
with fields SearchNumber,price,quantity,shippeddate

Customer returns are included in the table with a negative quantity


Example:
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:

SearchNumber
Partsold
SalesTotal
AveragePrice
NumberOfSales

My summation query for this part shows:

Partsold: 702
SalesTotal: $366:00
AveragePrice: $.521
NumberOfSales: 4

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.

Thanks


SLQ Code:
SELECT qryOrdersThatMeetMOQ.SearchNumber, Sum(qryOrdersThatMeetMOQ.Quantity) AS PartsSold, Sum([quantity]*[Price]) AS SalesTotal, [SalesTotal]/[PartsSold] AS AverageUnitPrice, Count(qryOrdersThatMeetMOQ.InvoiceNumber) AS NumberOfSales
FROM qryOrdersThatMeetMOQ
GROUP BY qryOrdersThatMeetMOQ.SearchNumber
HAVING (((qryOrdersThatMeetMOQ.SearchNumber) Is Not Null And (qryOrdersThatMeetMOQ.SearchNumber)<>""));
pcalabriaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Actually, from the data you provided, it looks like they purchased 815 of this item, and returned 813, so you have three purchases.

Unless you have the invoice number in there somewhere too, there is no way to relate the 813 returned items to the 815 sold items.  Is there an invoice number field as well?
0
pcalabriaAuthor Commented:
Oh, right.  Three orders, that makes sense.
Yes.   I do have an invoicenumber field.. note the sql code above.

 Count(qryOrdersThatMeetMOQ.InvoiceNumber) AS NumberOfSales

I am counting invoice numbers to determine the number of sales.
0
pcalabriaAuthor Commented:
This is still an issue.  Can anyone help?
0
Dale FyeCommented:
OK, so lets start by reducing your original data and see if we can get the 3 invoices with remaining purchases.

SELECT SearchNumber
, InvoiceNumber
, Price
, Sum(Quantity) AS PartsSold
, Sum([quantity]*[Price]) AS SalesTotal
FROM qryOrdersThatMeetMOQ
WHERE SearchNumber = "006207341914000"
GROUP BY SearchNumber, InvoiceNumber, Price

That should result in:
SearchNumber         Invoice #     Price    Quantity    Amount
006207341914000         X          $0.65       100         65
006207341914000         Y          $0.50         2          1
006207341914000         Z          $0.50       600        300

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcalabriaAuthor Commented:
Perfect!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.