Solved

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

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
default value of combobox 25 70
unknown computers in my LAN 12 100
Speed up Select Top n... Query 9 37
MS Access 2016 resize forms 3 10
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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 Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

739 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