Solved

I don't know how to pull this specific information using SQL in MS Access?

Posted on 2009-04-06
2
168 Views
Last Modified: 2012-05-06
This shows all the upc's that have been sold in the past month.

I want to show all upc's that have been sold TWICE in the past month and have a quantity of less than 5.
SELECT UPC
 
FROM salesitems, salelineitem
 
Where salelineitem.sku = salesitems.sku
 
and exists
 
 (select sku
from saleLineitem, salesno
where salelineitem.saleno = salesno.saleno
and Exists (Select * From salesitems, salelineitem Where salesitems.sku = salelineitem.sku
and salelineitem.sku < 5)
and SalesNo.SaleDate <= DATE()
AND SalesNo.SaleDate >= DATEADD("m", -1, DATE()) );

Open in new window

0
Comment
Question by:jnreinsch
[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
2 Comments
 
LVL 12

Accepted Solution

by:
koutny earned 125 total points
ID: 24085423
First of all I would get rid of the exists conditions and use joins instead - the performance will be much better.

Secondly, I would create an auxiliardy query (say it's called query_number_of_sales) which would provide the number of sales in the past month for all products with sale quantity < 5.

Then a separate query could show the list of products which have been sold exactly twice, or at least twice etc.
--SQL code for query_number_of_sales:
 
SELECT saleLineitem.sku, Count(saleLineitem.sku) as NumberOfSales
FROM  saleLineitem INNER JOIN salesno
ON  salelineitem.saleno = salesno.saleno
WHERE salelineitem.sku < 5
AND SalesNo.SaleDate <= Date()
AND SalesNo.SaleDate >= Dateadd("m", -1, Date()) 
GROUP BY saleLineitem.sku
 
--final query:
 
SELECT SI.UPC
 FROM salesitems SI INNER JOIN query_number_of_sales QNS ON 
 SI.sku = QNS.sku
 
Where QNS.NumberOfSales = 2

Open in new window

0
 

Author Closing Comment

by:jnreinsch
ID: 31567364
thanks very much I'm new to this SQL stuff.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …

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