Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-07-28
5
Medium Priority
?
278 Views
Last Modified: 2014-08-08
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)<>""));
0
Comment
Question by:pcalabria
  • 3
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40224981
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
 

Author Comment

by:pcalabria
ID: 40225407
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
 

Author Comment

by:pcalabria
ID: 40228588
This is still an issue.  Can anyone help?
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40228744
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
 

Author Closing Comment

by:pcalabria
ID: 40248331
Perfect!  Thank you!
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

772 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