Duplicate Account Numbers

[ecugrad]

Looking for some help. I have two fields, AccountNumber and Disposition. Disposition can be a "S"(suspended) or D (Delivered). I want a report listing all the accountnumbers that have an "S" in the Disposition field. The tricky part is that when an account is taken out of Suspension it creats another object with the same account number. That means I can have account 123 w, a "D" in Disposition and account 123 w/ a "S" in Suspension.

All I want on the report is accounts w, "S" in disposition that don't have a duplicate w/ a "D" in Disposition. That is if an account 123 has a "D" and 123 has a "S", I don't want it. If account 123 has a "S" and it is not in database w/ a "D", I want it.

Hopefully I haven't made the above to confusing.. Thanks for any help..

Mike

 

[rhinok]

I thought of two ways to do this, here's the simplest method of the two.

1) Create a Group for Acct.
2) Create a Max of Disposition for the Group - as long as you only have 'D' and 'S' dispositions, this will work.
3) Create a Count of Disposition
4) Edit your Group Selection Criteria similar to this:

Maximum ({Table1.Disposition}, {Table1.AcctNumber}) = 'S' and
Count ({Table1.Disposition}, {Table1.AcctNumber}) = 1

5) Suppress your Group Headers and Footers for transparency to the users

One question: Is it possible that an account can have multiple D's and S'? If this is the case, you will probably need a more complex method.

 

[kenhamady]

Is there ANY way to look at a single record and know that it is one you want without checking for the existance of other records in the group? ie, is there a mark in the S record when the D is added?

If not, you will have to use a group selectiion criteria as rhinok has described. Use running totals for any totals, since normal totals don't adjust for group selection. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/