"exclude" query 1

[greenfibres]

Hi all - hope you can help

I have 2 tables
table 1 = customers
on this table is a 'firstcontact' field

table 2 = orders
on this table is a 'orderdate' field

I am trying to find all customers who had first contact between 01/01/1997 and 31/12/1998 inclusive and who ONLY ordered since 01/04/2005
My problem is that I don't know how to exclude people who ordered BOTH before 01/04/2005 and after 01/04/2005.

Thanks in advance for your help.
All the best
Jim

 

[PHV]

Something like this ?
SELECT C.customer, C.firstcontact, MIN(rderdate) AS FirstOrderDate
FROM customers AS C INNER JOIN orders AS O ON O.custID = C.custID
WHERE C.firstcontact BETWEEN #1997-01-01# And #1998-12-31#
GROUP BY C.customer, C.firstcontact
HAVING MIN(rderdate) >= #2005-04-01#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[greenfibres]

Thanks for getting back so quickly
Unfortunately I'm very much a begginer with this and i am not so sure what your reply means! :-(
Should I be able to enter this into the criteria field of the query?
I tried and it gave me the message
"check the subquery's syntax and enclose the subquery in parentheses"

I hope you're willing to walk me through it.

All the best
Jim

 

[greenfibres]

I must admit that I don't have a clue as to why you type
C.customer, C.firstcontact,
Sorry - I hope I'm not too illiterate for this forum!

 

[PHV]

My suggestion was SQL code, not criteria in the query grid.
Choose SQL as display mode.
You may have to change the JOIN condition as you didn't tell us how are customers and orders related.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

Write a query with a NOT EXISTS condition.

A query for the people that you wish to exclude.

SELECT * FROM Orders
WHERE  orderdate < #01/04/2005#

That is the essence of a subquery used with NOT EXISTS.

SELECT a.* FROM Customers
WHERE a.firstcontact BETWEEN #01/01/1997# AND #31/12/1998#
  AND NOT EXISTS (
         SELECT * FROM Orders
         WHERE orderdate < #01/04/2005# 
           AND customerID = a.customerID
        )

In other words, given a customerID, look for rows in the Orders table for that customer and prior to April 1, 2005. If such rows do not exist then include this customer. Provided their first contact was between Jan 1, 1997 and Dec 31, 1998.

FYI, the peculiar syntax inside the parentheses is a "correlated subquery". Think of it as performing the subquery for each row in the outer query and taking values from that row to limit the results of the subquery. In this case, only look at order rows for the one customerID currently being processed in the outer query. Sorry, if my explanation has just confused the issue.

Another approach is to use the subquery with NOT IN.

SELECT * FROM Customers
WHERE firstcontact BETWEEN #01/01/1997# AND #31/12/1998#
  AND customerID NOT IN (
         SELECT customerID FROM Orders
         WHERE  orderdate < #01/04/2005# 
        )

hth

 

[greenfibres]

Hi, and thanks for your replies!
The above select query didn't quite work for me, but it taught me what I needed to know, and ended up using

In (SELECT CustomerID FROM Customers WHERE firstcontact BETWEEN #01/01/1997# AND #31/12/1998# AND customerID NOT IN (SELECT customerID FROM Orders WHERE orderdate < #01/04/2005#))

It runs very slowly for some reason (30000 names and 27000 orders in the 2 tables), but it does the job!
Any ideas about speeding it up would be useful.

All the best
Jim

 

[PHV]

Any ideas about speeding it up would be useful
Have you tried my suggestion ?