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
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 3,736
Thanks: 46
Thanked 1,267 Times in 1,199 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about
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: 4,187
Thanks: 80
Thanked 1,141 Times in 1,117 Posts
Minty will become famous soon enough Minty will become famous soon enough
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())

__________________
A little thanks goes a long way
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

(Please use the scales on the left if we have helped!) Mark threads as Solved once you have an answer.

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: 128
Thanks: 4
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 10 06-14-2011 08:22 AM
[SOLVED] 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:52 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 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World