Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2009-04-06
2
Medium Priority
?
183 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 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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. …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
From store locators to asset tracking and route optimization, learn how leading companies are using Google Maps APIs throughout the customer journey to increase checkout conversions, boost user engagement, and optimize order fulfillment. Powered …

584 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