Report of Products NOT selling

[Gavin27]

Have table of Products. Have Select Query of sales by customer (sum of all
quantities of products sorted by customer). But can not figure how to run a
query to filter out all products selling, leaving only products not selling
for a report. Suspect i am thinking about it the wrong way. If anyone can
help, much apprreciated

 

[John Vinson]

Have table of Products. Have Select Query of sales by customer (sum of all
quantities of products sorted by customer). But can not figure how to run a
query to filter out all products selling, leaving only products not selling
for a report. Suspect i am thinking about it the wrong way. If anyone can
help, much apprreciated

Use the "Unmatched Query Wizard" to find all items in Products that do
not exist in Sales.

If you want to find all products that have not sold in (say) the last
six months, first create a Query finding all the products that HAVE
sold in that period:

SELECT ProductID
FROM Sales
WHERE SaleDate > DateAdd("m", -6, Date());

Save this as qrySold; then create another query

SELECT Products.ProductName, <other fields as needed>
FROM Products LEFT JOIN qrySold
ON Products.ProductID = qrySold.ProductID
WHERE qrySold.ProductID IS NULL;

John W. Vinson[MVP]

 

[Larry Linson]

Have table of Products. Have Select Query of
sales by customer (sum of all quantities of products
sorted by customer). But can not figure how to run a
query to filter out all products selling, leaving only
products not selling for a report. Suspect i am thinking
about it the wrong way. If anyone can help,
much apprreciated

As I was walking up the stair,
I met a man who wasn't there.
He wasn't there again today.
I wish, I wish he'd go away.
~Hugh Mearns

The problem is that a table of _sales_ does not normally include information
about that which was _not_ sold, so you can't select those in a query
against your table of sales.

What you will need to do is to create a query that includes a table of the
products (that's usually available in databases dealing with products,
orders, sales, etc.), joined by the field you use for product
identification, to the table where sales are recorded.

Use a criteria of Null under some field from the sales information, and the
result should be what you want... a list of the products that didn't sell at
all.

Larry Linson
Microsoft Access MVP

 

[Tom Wickerath]

Hi Gavin,

First, it is helpful to define what you mean by not selling. Typically, this
might include a time period, and perhaps a minimum number of units. Try
creating the following two queries in the sample Northwind database. The
first query is a parameter query that has you enter the minimum number sold
to be considered "selling", along with a starting and ending date. You can
just as easily have these parameters picked up from text boxes on an open
form:

qryProductsSold

PARAMETERS [Enter Minimum Units] Long,
[Enter Starting Date] DateTime, [Enter Ending Date] DateTime;
SELECT [Order Details].ProductID,
Count([Order Details].ProductID) AS CountOfProductID
FROM Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)
Between [Enter Starting Date] And [Enter Ending Date]))
GROUP BY [Order Details].ProductID
HAVING (((Count([Order Details].ProductID))>=[Enter Minimum Units]));


Note: Valid date range is

We will now use the above query to find those products that did not sell:

qryProductsThatDidNotSell

SELECT Products.ProductID, Products.ProductName
FROM Products
LEFT JOIN qryProductsSold ON Products.ProductID = qryProductsSold.ProductID
WHERE (((qryProductsSold.ProductID) Is Null));


You could also do the first query as a subquery.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Tom Wickerath]

PS.

Note: Valid date range is 04-Jul-1996 to 06-May-1998 for a "clean" copy of
Northwind (pre Access 2007).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Hi Gavin,

First, it is helpful to define what you mean by not selling. Typically, this
might include a time period, and perhaps a minimum number of units. Try
creating the following two queries in the sample Northwind database. The
first query is a parameter query that has you enter the minimum number sold
to be considered "selling", along with a starting and ending date. You can
just as easily have these parameters picked up from text boxes on an open
form:

qryProductsSold

PARAMETERS [Enter Minimum Units] Long,
[Enter Starting Date] DateTime, [Enter Ending Date] DateTime;
SELECT [Order Details].ProductID,
Count([Order Details].ProductID) AS CountOfProductID
FROM Orders
INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)
Between [Enter Starting Date] And [Enter Ending Date]))
GROUP BY [Order Details].ProductID
HAVING (((Count([Order Details].ProductID))>=[Enter Minimum Units]));


Note: Valid date range is

We will now use the above query to find those products that did not sell:

qryProductsThatDidNotSell

SELECT Products.ProductID, Products.ProductName
FROM Products
LEFT JOIN qryProductsSold ON Products.ProductID = qryProductsSold.ProductID
WHERE (((qryProductsSold.ProductID) Is Null));


You could also do the first query as a subquery.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Have table of Products. Have Select Query of sales by customer (sum of all
quantities of products sorted by customer). But can not figure how to run a
query to filter out all products selling, leaving only products not selling
for a report. Suspect i am thinking about it the wrong way. If anyone can
help, much apprreciated