Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-18-2019, 10:47 PM   #1
Scott488
Newly Registered User
 
Join Date: Nov 2015
Posts: 13
Thanks: 3
Thanked 0 Times in 0 Posts
Scott488 is on a distinguished road
Unmatched in the last 30 days

Hi all, there may be a simple solution to this, but it isn't leaping out at me this morning.

Two tables, customers and orders and I want to know who hasn't placed an order in the last 30 days.

Scott488 is offline   Reply With Quote
Old 08-18-2019, 11:02 PM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,355
Thanks: 67
Thanked 2,682 Times in 2,568 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Unmatched in the last 30 days

something like:

select customers.[customer id], customers.[customer name]
from
customers
where customers.[customer id] not in (select [customer id] from orders
where orders.[order date] >= date()-30)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-18-2019, 11:04 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,328
Thanks: 112
Thanked 2,838 Times in 2,588 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Unmatched in the last 30 days

Use an unmatched query on both tables filtering for CustomerID Is Null in the Orders table AND OrderDate Between Date and Date-30.

Use the unmatched query wizard to make life simple

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
Old 08-19-2019, 06:03 AM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,112
Thanks: 15
Thanked 1,566 Times in 1,489 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Unmatched in the last 30 days

Although arne's solution will work, I don't like to use subqueries. Access doesn't optimize them well so they can be slow and they are hard to read in QBE view. Subqueries are fine if the BE is SQL Server but I still find them hard to read in QBE.

I would go with an unmatched query as Colin suggested but the problem with this is that Access will not return what you expect when you use a LEFT join and the criteria is on the RIGHT side table. Therefore, I create a query for the rightside table with criteria to limit the orders to the past 30 days and then use a left join of the customer to the query.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman 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
Number of days between two dates with irregular days worked JPed Queries 1 04-14-2014 10:28 PM
Convert Weekend and holiday days to work days mr moe General 1 06-14-2013 10:25 AM
Date Add problem (calendar days vs working days) bdhtexas Queries 8 10-13-2010 03:56 AM
Chart displays all days even days with no data tinman Forms 0 07-08-2009 03:16 AM
Making a text box count days but take away weekend days swarv Modules & VBA 2 12-11-2008 03:07 AM




All times are GMT -8. The time now is 07:42 AM.


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

Featured Forum post


Sponsored Links


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