Report on Customers who've not ordered in the last 3 months

[Siggy]

Hi, I'm new here, so go gentle with me!

here's my issue (and I'm sure the answer is simple):

Say I have a DB with Customers and Orders. I want to produce a report of all customers who have not ordered in the last 3 months.

I know how to filter out the last 3 months, but I obviously need to exclude those who have ordered in the last 3 months. (simple eh! )

 

[RichO]

You'll want to create a query to use as the record source for your report.

For the criteria under the customer name:

Not In (SELECT CustomerName FROM MyTable WHERE OrderDate > DateAdd("m", -3, Date()))
(Of course, change the field and table names to match your database)

What this is doing is using a sub-query of all customers who have ordered in the last 3 months, and then "Not In" excludes all customers found in that sub-query.

Hope that helps.

 

[Siggy]

Thanks RichO

placed this string:

Select CustID
From
Cust
Where Not In (SELECT CustID FROM CustOrder WHERE OrderDate > DateAdd("m", -3, Date()))

but get missing operator error. If I replace In with Exists, it goes through but returns no rows

Siggy

 

[RichO]

You're missing a reference to the CustID field in the WHERE clause...

Select CustID
From
Cust
Where CustID Not In (SELECT CustID FROM CustOrder WHERE OrderDate > DateAdd("m", -3, Date()))


Also, you are referencing 2 table names:

Select CustID
From
Cust
Where CustID Not In (SELECT CustID FROM CustOrder WHERE OrderDate > DateAdd("m", -3, Date()))

...both of these must be the same (the name of your table).

 

[Siggy]

OOPs Thanks RichO