Solved

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

Posted on 2009-04-06
2
162 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
2 Comments
 
LVL 12

Accepted Solution

by:
koutny earned 125 total points
Comment Utility
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
Comment Utility
thanks very much I'm new to this SQL stuff.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

6 Experts available now in Live!

Get 1:1 Help Now