Help for finding customers with no orders query

  • Thread starter Thread starter John Bildy
  • Start date Start date
J

John Bildy

I have an orders database. I want to run a query to find customers with no orders, or, that haven't ordered for some time. Can't seem to make one that works.

I set up a query looking for cutomers with no OrderID's. Tried using Is Null on different fields but it doesn't work.

I have:

Orders table: OrderID, OrderDate, CustID
OrderDetails Table: OrderID, ProductID, Qty, Arrived, Collected

Any help, once again, much appreciated.
 
John Bildy said:
I have an orders database. I want to run a query to find customers
with no orders, or, that haven't ordered for some time. Can't seem to
make one that works.

I set up a query looking for cutomers with no OrderID's. Tried using
Is Null on different fields but it doesn't work.

I have:

Orders table: OrderID, OrderDate, CustID
OrderDetails Table: OrderID, ProductID, Qty, Arrived, Collected

Any help, once again, much appreciated.
Click to expand...

Presumably you also have a Customers table with the primary key CustID.
Assuming that is so, try this for customers with no orders:

SELECT
Customers.CustID
FROM
Customers LEFT JOIN Orders
ON Customers.CustID = Orders.CustID
WHERE
OrderID Is Null;

For customers without orders in, say, the last year, try this:

SELECT
Customers.CustID
FROM
Customers LEFT JOIN
(SELECT
Orders.CustID
FROM
Orders
WHERE
DateDiff("d", OrderDate, Date()) < 366
) As O
ON Customers.CustID = O.CustID
WHERE
O.CustID Is Null;

That's "air SQL", and I'm no great SQL wizard, so it may be wrong. Let
me know.
 
Hi Dirk,

First one worked, 2nd didn't.

This is what I now have and it works great.

SELECT Customers.*
FROM Customers LEFT JOIN Orders ON
[Customers].[CustomerID]=[Orders].[CustomerID]
WHERE ((([Orders].[CustomerID]) Is Null));

Cheers,
 
John Bildy said:
Hi Dirk,

First one worked, 2nd didn't.

This is what I now have and it works great.

SELECT Customers.*
FROM Customers LEFT JOIN Orders ON
[Customers].[CustomerID]=[Orders].[CustomerID]
WHERE ((([Orders].[CustomerID]) Is Null));
Click to expand...

I just set up tables to match yours, and the second one seems to work
fine for me. In what way didn't it work for you? Did you paste the SQL
I gave you into the SQL view of a new query?

This is what I have, and it works for me, returning customers who have
no orders in the last 365 days:

SELECT
Customers.CustID
FROM
Customers LEFT JOIN
(SELECT
Orders.CustID
FROM
Orders
WHERE
DateDiff("d", OrderDate, Date()) < 366
) As O
ON Customers.CustID = O.CustID
WHERE
O.CustID Is Null;
 
Hi again Dirk,

I copied and pasted as below and it worked OK. First time it came back with
an error something along the lines of wrong syntax in the FROM statement.
Can't remember exactly. But this one works fine.

I want to change this one so that user can enter a date <=[Enter a date].
Where would I put this in the SQL?

Thanks for the trouble you have gone to for me. It's very much appreciated.
 
John Bildy said:
Hi again Dirk,

I copied and pasted as below and it worked OK. First time it came
back with an error something along the lines of wrong syntax in the
FROM statement. Can't remember exactly. But this one works fine.
Click to expand...

I suspect that you ran into one of Access's little quirks. When you
save a query with a "derived table" like that -- an SQL SELECT statement
acting as if it were a table itself -- Access converts the parenthesized
SQL statement, which was written like this ...

(SELECT
Orders.CustID
FROM
Orders
WHERE
DateDiff("d", OrderDate, Date()) < 366
) As O

.... into its own idiosyncratic format, something like this:

[SELECT
Orders.CustID
FROM
Orders
WHERE
DateDiff("d", OrderDate, Date()) < 366
]. AS O

Unfortunately, if you aren't careful in subsequent editing, Access may
attempt to put brackets around table or field names inside the derived
table's SQL statement, and that will interfere with the subsequent
parsing of that statement. This is a bug, in my opinion, but there you
have it. Unfortunately, parameters within the query must also be
bracketed, so that makes it difficult to do what you ask here:
I want to change this one so that user can enter a date <=[Enter a
date]. Where would I put this in the SQL?
Click to expand...

Difficult, I say, but not impossible. As long as you don't attempt to
edit the SQL of the query after you have saved it, you can write this:

SELECT
Customers.CustID
FROM
Customers
LEFT JOIN
(SELECT
Orders.CustID
FROM
Orders
WHERE
OrderDate >= [No orders since when?]
) AS O
ON Customers.CustID = O.CustID
WHERE O.CustID Is Null;

When you save that, it will be converted to this:

SELECT Customers.CustID
FROM Customers LEFT JOIN [SELECT
Orders.CustID
FROM
Orders
WHERE
OrderDate >= [No orders since when?]
]. AS O ON Customers.CustID = O.CustID
WHERE (((O.CustID) Is Null));

which will still work so long as you don't edit it. If you edit it,
though, Access will refuse to save it now, because it can't make sense
of the brackets-in-brackets.

If you want to avoid this problem, there are other ways to write queries
that return the same results. Here's one version that works:

SELECT
Customers.CustID
FROM
Customers
WHERE
Customers.CustID Not In
(SELECT Orders.CustID FROM Orders
WHERE OrderDate >= [No orders since when?]);

This version uses a subquery that makes your intention much plainer, and
it isn't subject to the bracket-parsing problem that is so annoying with
the derived-table version. The only flaw is that this version is *much*
less efficient that the other. It seems Jet (the database engine used
by Access) doesn't handle "Not In" clauses very well. But try this
version. If it gives acceptable performance in your application, by all
means go with it.

Another, better-performing but clunkier solution to the problem is to
use a derived table as in the first approach, but break that query out
as a separate stored query. That is, have "qryNoOrdersSinceWhen" with
SQL like this:

SELECT
Orders.CustID
FROM
Orders
WHERE
OrderDate >= [No orders since when?];

This query exists for the sole purpose of being used in your final
query, "CustsWithoutRecentOrders", which has this SQL:

SELECT
Customers.CustID
FROM
Customers
LEFT JOIN
qryNoOrdersSinceWhen
ON Customers.CustID = qryNoOrdersSinceWhen.CustID
WHERE qryNoOrdersSinceWhen.CustID Is Null;

That gets around the bracketing problem, but of course now you have an
extra query floating around.

There are probably other ways of solving the same problem, but as I said
I'm no great SQL maven, and the above should give you enough viable
alternatives.
 
Excellent. Thanks for the ideas. I will try them out and let you know -
maybe tomorrow.
--
John B

Dirk Goldgar said:
John Bildy said:
Hi again Dirk,

I copied and pasted as below and it worked OK. First time it came
back with an error something along the lines of wrong syntax in the
FROM statement. Can't remember exactly. But this one works fine.
Click to expand...

I suspect that you ran into one of Access's little quirks. When you
save a query with a "derived table" like that -- an SQL SELECT statement
acting as if it were a table itself -- Access converts the parenthesized
SQL statement, which was written like this ...

(SELECT
Orders.CustID
FROM
Orders
WHERE
DateDiff("d", OrderDate, Date()) < 366
) As O

... into its own idiosyncratic format, something like this:

[SELECT
Orders.CustID
FROM
Orders
WHERE
DateDiff("d", OrderDate, Date()) < 366
]. AS O

Unfortunately, if you aren't careful in subsequent editing, Access may
attempt to put brackets around table or field names inside the derived
table's SQL statement, and that will interfere with the subsequent
parsing of that statement. This is a bug, in my opinion, but there you
have it. Unfortunately, parameters within the query must also be
bracketed, so that makes it difficult to do what you ask here:
I want to change this one so that user can enter a date <=[Enter a
date]. Where would I put this in the SQL?
Click to expand...

Difficult, I say, but not impossible. As long as you don't attempt to
edit the SQL of the query after you have saved it, you can write this:

SELECT
Customers.CustID
FROM
Customers
LEFT JOIN
(SELECT
Orders.CustID
FROM
Orders
WHERE
OrderDate >= [No orders since when?]
) AS O
ON Customers.CustID = O.CustID
WHERE O.CustID Is Null;

When you save that, it will be converted to this:

SELECT Customers.CustID
FROM Customers LEFT JOIN [SELECT
Orders.CustID
FROM
Orders
WHERE
OrderDate >= [No orders since when?]
]. AS O ON Customers.CustID = O.CustID
WHERE (((O.CustID) Is Null));

which will still work so long as you don't edit it. If you edit it,
though, Access will refuse to save it now, because it can't make sense
of the brackets-in-brackets.

If you want to avoid this problem, there are other ways to write queries
that return the same results. Here's one version that works:

SELECT
Customers.CustID
FROM
Customers
WHERE
Customers.CustID Not In
(SELECT Orders.CustID FROM Orders
WHERE OrderDate >= [No orders since when?]);

This version uses a subquery that makes your intention much plainer, and
it isn't subject to the bracket-parsing problem that is so annoying with
the derived-table version. The only flaw is that this version is *much*
less efficient that the other. It seems Jet (the database engine used
by Access) doesn't handle "Not In" clauses very well. But try this
version. If it gives acceptable performance in your application, by all
means go with it.

Another, better-performing but clunkier solution to the problem is to
use a derived table as in the first approach, but break that query out
as a separate stored query. That is, have "qryNoOrdersSinceWhen" with
SQL like this:

SELECT
Orders.CustID
FROM
Orders
WHERE
OrderDate >= [No orders since when?];

This query exists for the sole purpose of being used in your final
query, "CustsWithoutRecentOrders", which has this SQL:

SELECT
Customers.CustID
FROM
Customers
LEFT JOIN
qryNoOrdersSinceWhen
ON Customers.CustID = qryNoOrdersSinceWhen.CustID
WHERE qryNoOrdersSinceWhen.CustID Is Null;

That gets around the bracketing problem, but of course now you have an
extra query floating around.

There are probably other ways of solving the same problem, but as I said
I'm no great SQL maven, and the above should give you enough viable
alternatives.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top