Solved

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

Posted on 2009-04-06
2
165 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

14 Experts available now in Live!

Get 1:1 Help Now