Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-14-2017, 01:06 AM   #1
DaveCollins181
Newly Registered User
 
Join Date: Oct 2014
Posts: 45
Thanks: 10
Thanked 0 Times in 0 Posts
DaveCollins181 is on a distinguished road
Query Supplier List Who Have Not Placed Order In Over 2 Years

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.

DaveCollins181 is offline   Reply With Quote
Old 09-14-2017, 01:18 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,136
Thanks: 54
Thanked 1,968 Times in 1,881 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Query Supplier List Who Have Not Placed Order In Over 2 Years

selkect supliers. Id from supliers left join [purchase orders] on supliers. Id=[purchase orders]. Id where [purxhase orders]. [Order date] <= datediff("y", -2, date)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-14-2017, 01:20 AM   #3
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,292
Thanks: 124
Thanked 1,435 Times in 1,407 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Query Supplier List Who Have Not Placed Order In Over 2 Years

In your query you want the following criteria under the order date;

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

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-14-2017, 02:13 AM   #4
Peter Reid
Newly Registered User
 
Join Date: Dec 2003
Location: Somerset
Posts: 132
Thanks: 10
Thanked 20 Times in 19 Posts
Peter Reid is on a distinguished road
Re: Query Supplier List Who Have Not Placed Order In Over 2 Years

Quote:
Originally Posted by arnelgp View Post
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
Peter Reid is offline   Reply With Quote
Old 09-14-2017, 07:50 AM   #5
DaveCollins181
Newly Registered User
 
Join Date: Oct 2014
Posts: 45
Thanks: 10
Thanked 0 Times in 0 Posts
DaveCollins181 is on a distinguished road
Re: Query Supplier List Who Have Not Placed Order In Over 2 Years

Thanks everyone for the answers detailed above. I will give these a try as soon as I get a chance.


David

DaveCollins181 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort OrderedItem list, assign 1 OrderNum per Supplier & send email T0bias Modules & VBA 15 05-24-2017 06:35 AM
List of years with totals of hours wim1981 Forms 1 07-01-2015 10:00 PM
Creating a Query to list all Months and Years from Jan 06 to Present date romit91 Queries 8 06-14-2011 08:22 AM
list all the years in a combo box Jan_W Forms 8 05-23-2006 05:08 PM
Drop down list of years Dave_cha Forms 9 10-11-2005 05:17 PM




All times are GMT -8. The time now is 11:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World