Unmatched in the last 30 days

[Scott488]

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.

 

[arnelgp]

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)

 

[isladogs]

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

 

[Pat Hartman]

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.