>The only duplicate items in the record would be the amount=0.00, settdate=03/20/2001, bankname and filename, There are other fields with disinct items in them.
You say bankname is duplicated. In that case, you are only going to get distinct values for bankname; nothing else.
Ex.
bankname amount setdate filename otherfield1
Bank1 1.25 03/20/2001 file1 X
Bank1 1.50 03/19/2001 file2 Y
Bank2 2.00 03/20/2001 file1 Z
select bankname,sum(amount) as 'amount',count(*) as 'cnt' from mytable group by bankname
If you use the SQL statement above, you will get the following results:
bankname amount cnt
Bank1 2.75 2
Bank2 2.00 1
Do you have 24 occurrences of a single bankname and the count() is only returning 22 of them but the sum() function is picking up the values associated with those other 2?
If the sum() picks up the records, the count() will pick them up. As others have stated, SET DELETE ON and the Delete flag will have an effect on this but it isn't an either or situation. Both would have a problem or neither would.
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao