Suppress Entire Records?

[Crazycritter1]

I have a crystal report in progress, that can give me the following information:

Acct# Name Address Card

1111 Joe 1111 Main ATM
1111 Joe 1111 Main GLD

2222 Mary 2222 Elm

3333 Bill 3333 Birch DBC

4444 Ted 4444 Cherry VIS


Here is my scenario:

I have to be able to suppress/exclude any record that has the occurance of DBC or ATM in the card field. In the example above, account 1111 has 2 entries, one for the ATM and one for the GLD. The presence of ATM in either of his records would qualify him for the exclusion of both.

The presence of DBC in account 3333 would exclude by the criteria as well.

If we cannot fully exclude the record from the report, I'd like to suppress the main account number from the report, so that when I export to excel to create a database, I can sort and delete those records.

My desired output would be:


2222 Mary 2222 Elm
4444 Ted 4444 Cherry VIS

(Account 2222 has NO value for the card field whatsoever...)

Is there a good way to approach this?

Please and Thanks as usual...

 

[kskid]

See thread767-1030738 for a couple of solutions to your request.

By the way, it would also be helpful to include the Crystal version, database and type of connection since different version may require different solutions

Cheers,
-LW

 

[Crazycritter1]

I'm using Crystal version 10, with an SQL database, and ODBC connection... I think that was what you're after. I'm kindof a rookie at crystal, so please bare with me..

Thanks!

K.

 

[lbass]

To be specific about kskid's reference, insert a group on {table.acctno} and then create a formula {@ATMDBC}:

if {table.card} in ["ATM","DBC"] then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@ATMDBC},{table.acctno}) = 0

Once you have used group selection, you will need to use running totals for calculations across groups, since non-group selected records would contribute to inserted summaries.

You could also try the SQL expression approach in the thread kskid mentions.

-LB

 

[Crazycritter1]

I am already using a grouping for address, so I can suppress those who have multiple account numbers, and only send them 1 mailing.

If I change the grouping to account number, I lose that functionality, however, when I do this, I still get entries that should have been eliminated completely...

In my example above, instead of returning this:

2222 Mary 2222 Elm
4444 Ted 4444 Cherry VIS

I would have the additional entry of:

1111 Joe 1111 Main GLD

Which should have been eliminated, because his account was noted as also having an ATM card on the same account...

Is there a secondary formula I could try?

 

[lbass]

Change {@ATMDBC} to:

if isnull({table.card}) or
not({table.card} in ["ATM","DBC"]) then 0 else 1

Then, if you want to use the address field as the group, the group selection formula should be:

sum({@ATMDBC},{table.address}) = 0

-LB

 

[Crazycritter1]

Excellent - I'm still in the verification process, but I think that it worked like a charm! Thanks to both of you!