Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-07-28
5
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 48

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 48

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

721 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