Query Supplier List Who Have Not Placed Order In Over 2 Years (1 Viewer)

DaveCollins181

Registered User.
Local time
Today, 08:40
Joined
Oct 27, 2014
Messages
45
I have two tables.

1) Suppliers
2) Purchase Orders - Has order date field

Suppliers is linked to Purchase orders via the Supplier ID field. I want to query all suppliers who have not placed an order in over 2 years. Can anyone assist with the query formula?

Thanks,

David.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:40
Joined
May 7, 2009
Messages
19,169
selkect supliers. Id from supliers left join [purchase orders] on supliers. Id=[purchase orders]. Id where [purxhase orders]. [Order date] <= datediff("y", -2, date)
 

Minty

AWF VIP
Local time
Today, 08:40
Joined
Jul 26, 2013
Messages
10,353
In your query you want the following criteria under the order date;

Not >Dateadd("yyyy",-2,Date())
 

Peter Reid

Registered User.
Local time
Today, 08:40
Joined
Dec 3, 2003
Messages
134
selkect supliers. Id from supliers left join [purchase orders] on supliers. Id=[purchase orders]. Id where [purxhase orders]. [Order date] <= datediff("y", -2, date)

This will show a (possibly duplicated) list of suppliers who have a PO from before 2016, even if they have a PO from 2016 onwards

Minty corrected the condition to make it over 2 years but you will also have to find out when each suppliers last order was

Using a subquery, something like

Code:
SELECT s.SupplierID, s.SupplierName, po.LastOrderDate
FROM (SELECT SupplierID, Max(OrderDate) AS LastOrderDate
 FROM YourPurchaseOrderTable
 GROUP BY SupplierID) AS po RIGHT JOIN YourSupplierTable AS s ON po.SupplierID = s.SupplierID
WHERE (((po.LastOrderDate)<DateAdd("yyyy",-2,Date()) Or (po.LastOrderDate) Is Null))
ORDER BY s.SupplierID;

I've added an 'Is null' condition for suppliers that have never ordered
 

DaveCollins181

Registered User.
Local time
Today, 08:40
Joined
Oct 27, 2014
Messages
45
Thanks everyone for the answers detailed above. I will give these a try as soon as I get a chance.


David :)
 

Users who are viewing this thread

Top Bottom