newbie - selecting records without an entry in another tbl

[Guest_imported]

Hi all,

New to CR, and I have an Oracle database that I am trying to query. 3 Tables, let's call them T1, T2, and T3 for arguments sake. T1 has CustomerName and CustomerID. T2 has CustomerID, OrderID, and OrderDate. T3 has CustomerID, ServiceID, and ServiceDate. I want to know, for the month of March, which customers have an order entry, but not a service entry. I've been beating my head against a wall trying to figure this out, I know it is probably simple and I am going to kick myself when you tell me the simple answer. But if you could indulge me, I would appreciate it.

Thanks!
Vinnie

 

[kenhamady]

Acutally, this isn't simple but it can be done:

1) Link t1 to t2 (equal join) and t2 to t3 (Left outerJoin)
2) Put in a select criteria that says t2 has to be in march. Don't put ANY criteria on t3.

3) Put in a formula field that iss:

If t3 date is in march 2002
then 1
else 0

4) Group the report by customer and subtotal this last formula.

5) Put in a group selection formula that says this subtotal has to be = 0

6) Add a running total that does a distinct count of customer ID in t1.

I think that will give you all folks with t2 records in March and no t3 records in March.
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com