Eliminate accounts that have mulitple records

[Alicia159]

Hello,

I am running SQL 7 and Crystal 8.5 and need to figure out how to get a formula to eliminate records if a field shows up more than once...for example right now I have data

Acct Number Card type address
12345 ATM 123 maple
12345 Visa 123 maple
4321 Atm 1234 east
58745 Visa 5412 West

What I would want to do is search for account numbers with ONLY ATMS.

 

[campsys]

Go to
Report / EditSelectionFormula / RecordSelection

Add the following:

{Table.CardType} = "ATM"


//Replace {Table.CardType} with your field.
//This will only return records on the report that have the
//card type of "ATM

 

[Alicia159]

I realize that I could select all of the ATM records, but to get the information I need I also need to exclude people who have both an ATM and a Visa. So that I get only the people who have only an ATM card.

 

[lbass]

Group on {table.accountnumber} and then create a formula {@NotATM}:

if isnull({table.cardtype}) or
{table.cardtype} <> "ATM" then 1 else 0

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

sum({@NotATM},{table.accountnumber}) = 0

This will return on those accounts that contain only ATM card types. If you want to do further calculations, you will need to use running totals since non-group selected records still contribute to the usual, inserted summaries.

-LB