Archive Unordered Products 1

[SequelChik]

Hi, can anyone help me, my boss has asked me to archive all products that have not been ordered for the past 6 months.
I'm new to this but I know it is a Make-Table query, so I tried;
Select ProductID, OrderDate
From Product, Order (Inner Join,etc.......)
WHERE (((Order.OrderDate)<Date()-180));

but i think that this will show only products that have been ordered before 6 months and not include the ones that have never been ordered.

Can anyone help me please?
Thanx
SequelChik

 

[SBendBuckeye]

A subselect will find all of the never ordered products for you. Try the following to identify those products:

Select ProductID, Description
From Product
Where ProductID Not In
(Select Distinct ProductID
From Order)

The Subselect code above (what is inside the parenthesis) returns a list of all unique ProductIDs in the Order table. The outside code displays the ProductID and Description of every product Not in the order file.

If you are new to this then you are to be congratulated for thinking of never ordered parts as well.

You can combine them into one statement as below:

Select ProductID, OrderDate
From Product, Order (Left Join,etc.......)
WHERE (((Order.OrderDate)<Date()-180)) OR;
(ProductID Not In
(Select Distinct ProductID
From Order))

NOTE that for the above combined code to work you need to change your Inner Join to a Left Join. Otherwise the never ordered records will never show up because they do NOT have a matching record in the Order table.

Hope this helps and good luck!

 

[SequelChik]

Thanks a lot for your help, I am new to this and its so complicated!

SequelChik x