Need help with Crosstab Query for sales

[lorainguy]

Hey all, first post.

I have to pull a query to show all items in our inventory that have had no sales in the past 6 months.
End result is to show any items with stock that have no sales in the past 6 months. Its easy to pull numbers on items that HAVE sold, but finding the ones that havent is my issue. Any help is appreciated (I hope this all makes sense)
My Table structure is as follows:

[Titles] linked to [Order_Details] by TitleID
[Orders] linked to [Order_Details] by OrderID

[Titles]
TitleID
Title
Cost
Stock


[Orders]
OrderID
OrderDate


ShipDate
(various order/customer info)

[Order_Details]
OrderID
TitleID
ShippedQty
(various product details by order...cost, etc)

[lorainguy]

All I am looking for is a result that shows:

TitleID, Title, Shipped Qty
Where Shipped Qty = 0
Date rage < 6 mos

[orange]

I would approach the problem in 2 steps and Not as a Crosstab.

1- Find all Orders and Titles that have been Ordered in the last 6 months.Save query as OrdersLessThan6MonthsOld
2- Find all Title which are not in OrdersLessThan6MonthsOld

1 - OrdersLessThan6MonthsOld

SELECT Titles.Title, Titles.TitleId, OrderDetails.TitleId, Orders.Orderdate, Orders.OrderId
FROM (OrderDetails INNER JOIN Titles ON OrderDetails.TitleId = Titles.TitleId)
 INNER JOIN Orders ON OrderDetails.Orderid = Orders.OrderId
WHERE (((Orders.Orderdate)>=DateAdd("m",-6,Date())));

2- TitlesNotOrderedinLast6Months

SELECT Titles.TitleId, Titles.Title, OrdersLessThan6MonthsOld.Title
FROM Titles LEFT JOIN OrdersLessThan6MonthsOld ON Titles.Title = OrdersLessThan6MonthsOld.Title
WHERE (((OrdersLessThan6MonthsOld.Title) Is Null));

Sorry for any editing issues - I couldn't delete the little squares/rectangles.