Help for finding customers with no orders query.

[John Bildy]

I have an orders database. I want to run a query to find customers with no
orders. (All orders/order details are deleted when they have been picked up
by customer.)

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 much appreciated.

 

[Ken Snell]

Try this:

SELECT *
FROM Orders
LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
WHERE OrderDetails.OrderID Is Null;

 

[Tom Ellison]

Dear John:

Since it is likely that "customers with no orders" means customers who
have no rows in either the "Orders table" or the "OrderDetails Table"
then you'll have to find them in some other table, perhaps a
"Customer" table. I assume you have this table somewhere.

Perhaps something like this:

SELECT CustID, CustName
FROM Customer
WHERE CustID NOT IN (SELECT DISTINCT CustID FROM Orders)

If a customer has an order, but that order has not detail, is that an
order or not? If not, then:

SELECT CustID, CustName
FROM Customer
WHERE CustID NOT IN
(SELECT DISTINCT O.CustID FROM Orders O
INNER JOIN OrderDetails OD ON OD.OrderID = O.OrderID)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[John Bildy]

Returns all cutomers with orders instead of cut with no orders. My order ID
is autonumber. Does this affect this query in some way?
--
John B

"Ken Snell" > Try this:

SELECT *
FROM Orders
LEFT JOIN OrderDetails
ON Orders.OrderID = OrderDetails.OrderID
WHERE OrderDetails.OrderID Is Null;

 

[Van T. Dinh]

Try:

SELECT C.*
FROM Customers As C
LEFT JOIN Orders As O
WHERE O.CustomerID Is Null

 

[Tom Ellison]

Hey, Van, he'll need an ON clause for that LEFT JOIN. Probably this:

SELECT C.*
FROM Customers As C
LEFT JOIN Orders As O
ON O.CustID = C.CustID
WHERE O.CustomerID Is Null

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Van T. Dinh]

Thanks, Tom.

To be consistent, change the WHERE Clause to:

WHERE O.CustID Is Null.

 

[John Bildy]

Yeah. Thanks - that worked. This is what Access changed your code to:

YOUR CODE:
SELECT Customers.*
FROM Customers As Customers
LEFT JOIN Orders As Orders
ON Orders.CustomerID = C.CustomerID
WHERE Orders.CustomerID Is Null

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

What happened to the AS and what's with the brackets? (3 on left side and 2
on right of the WHERE statement.)

 

[John Bildy]

Hi Tom,

You have outlined it neatly indeed.

Anyway, this works:

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

That was the trick, knowing "customers with no orders" means customers who

have no rows in either the "Orders table" or the "OrderDetails Table"
then you'll have to find them in some other table, perhaps a
"Customer" table.

Regards,