Selecting customers who placed order before a specific date, but not after.

[Fredrik831]

Hi! Once again I come with my questions to this great forum!



I have two tables, Customer and Time. Everytime a customer places an order, the time and customer gets registered.

Now, I want to select all customers that have either placed an order more than one year back (>365) or never placed an order (null). But IF they have placed an order between today and one year back, they should not be selected.

I have managed to create a query that covers half om what I want to do, selecting customers with an order that are older than one year or never placed an order.

Here is my code so far:

SELECT DISTINCT Customer.CustomerID, Nz([Time.Tid],"NEVER") AS Last_order
FROM Customer LEFT JOIN Time ON Customer.CustomerID = Time.CustomerID
WHERE (((Time.Tid)<(Now()-365) Or (Time.Tid) Is Null));

How can I filter out the customers that have placed an order since then? The reason I want to do this is to be able to list these customers as "old".

Thanks in advance!

[orange]

Time is a reserved word in Access -- I see you have used [ ] to prevent an error.
I suggest you use the Date() function rather than Now().
eg Date gives you 3/23/2012 Now give 3/23/2012 5:17:37AM

You probably are not interested in minutes and seconds in >Year old Orders.

Would your table names be more meaningful as tblCustomer and tblCustomerOrders???

I do not understand this

How can I filter out the customers that have placed an order since then? The reason I want to do this is to be able to list these customers as "old".

You have 4 conditions that I see:

1)Customer has never placed an Order.
2)Customer has placed Order > 1 year ago from today and no Orders since
3)Customer has placed Order > 1 year ago from today and 1 or more Orders since
4)Customer has only placed Order less than 1 year ago from today

[Fredrik831]

My bad. In my database I have named the tables in swedish, and I translated them so you would more easily understand them. I have not encountered any problem yet with the Time table (in swedish "Tidpunkt").

You are correct, I'm not so interested in minutes and seconds, so it makes more sense to use Date() instead of Now().

Of those 4 conditions, I'm intrested in listing ONLY the customers of 1 and 2. But I'm not sure how to write that in SQL-code...

[orange]

I'm supposing you have tables something like the structures below. You can change your table/field names as needed.
The SQL is untested, but my best attempt without recreating your set up. Post back if there are issues.

Customer
CustomerId PK
other Customer specific info (Name, address...)

TimeXX
OrderId
CustomerId FK to Customer
OrderDate
other fields specific to this Order for this Customer on this date

1)Customer has never placed an Order.
SELECT CustomerId
from Customer LEFT JOIN TimeXX
on Customer.CustomerId = TimeXX.CustomerId
where TimeXX.CustomerId IS NULL;

2)Customer has placed Order > 1 year ago from today and no Orders since

SELECT Customer.CustomerId
from Customer INNER JOIN TimeXX
on Customer.CustomerId = TimeXX.CustomerId
Where Datediff("D",OrderDate, Date) >365
AND Customer.CustomerId NOT IN
(SELECT Customer.CustomerId
from Customer INNER JOIN TimeXX
on Customer.CustomerId = TimeXX.CustomerId
Where Datediff("D",OrderDate, Date) <365);

[Fredrik831]

Thank you very very much! It worked!

I combined the two, so now it does exactly what I want

[orange]

You are welcome. When you say you combined them --- was that a Union query?

[Fredrik831]

You are welcome. When you say you combined them --- was that a Union query?

No, I just used AND. SELECT Customer.CustomerId
from Customer INNER JOIN TimeXX
on Customer.CustomerId = TimeXX.CustomerId
Where Datediff("D",OrderDate, Date) >365
AND Customer.CustomerId NOT IN
(SELECT Customer.CustomerId
from Customer INNER JOIN TimeXX
on Customer.CustomerId = TimeXX.CustomerId
Where Datediff("D",OrderDate, Date) <365) AND TimeXX.CustomerId IS NULL;