Solved

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

Posted on 2014-07-28
5
251 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 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now