Help extracting the right information from tables 1

[rier]

I have two tables. One holds customer information, the other, a table that has the following;

CustomerID Status
1001046 A
1001047 D
1001047 P
1001047 D
1001048 D
1001048 D
1001049 P
etc...

I need to isolate only those customers where they have any ‘D’ status record(s), but no ‘P’ status records. In the above example, the only customer that would qualify would be 1001048. Each customer could have lots of ‘D’, lots of ‘P’, etc. The join between the [Customers] and [CustomerStatus] tables would be the CustomerID field.

Can anyone help?

Thanks

Richard

 

[BFAGG]

You could base the report on a Crystal Query which would be something like

SELECT fieldlist
FROM first_tablename
WHERE customer_id NOT IN
(SELECT customer_id FROM second_tablename
WHERE status = 'P')

Alternatively, you could create a database view using similar SQL and base the report on this.

 

[FoxG]

Hi,

If you group on the CustomerID field, then you can use two running totals and a detail suppression formula to determine whether this ID will be required.

Create two running totals based on the ID field which will be reset to zero on change of CustomerID ...

The first will select only those where status = "D"
The second where status = "P"

You detail suppression will be something like ...
{count of D} = 0 or {count of P > 0}


Hope this helps,

Geoff