Selection Formula Help 1

[kovas]

I am trying to redo an old report. I have 2 tables. People and Refer (Actually more but to simplify i'll just use the 2 main ones). Refer table may have one or more referrals for each customer. Each referral has a status. Lets say A (accepted), R (rejected) and I (incomplete). I need to select all the customers that were not A (accepted). The problem with this is that some customers have more than one record with all 3 status's. If I select all <> A this would also select records of people who were R (rejected) or I (incomplete) but were later A (accepted). Which is what I am trying to avoid. Basicly what I want to do is select customers that were not A (accepted) but if they were A (accepted) to reject the other records they might have.

The way I do this now is I select all the customers. Group it by customer name to avoid duplicates. Put all my info in the group header. Details is suppressed. Sort it by Referral Status Asc. To get A (accepted) to the top. Then in the group format section if status = A I suppress it. If its not = A i suppress all but the 1st rec. Because of all the suppressed records I have to do a formula Counter with Init, Add, Show to get an accurate count.

Anyways its a real hassle since I have a few reports like this. I know there has to be an easier way to do this.

Hope this makes sense, all suggestions more than welcome

thank you.

 

[kovas]

Example:

People:
People.ID, People.Name
1, John
2, Bill
3, James
4, Jim

Refer:
Refer.ID, People.ID, Refer.Status.
1, 1, R
2, 1, A
3, 1, I
4, 2, A
5, 3, I
6, 4, R
7, 4, I

From sample refer table I need to select every People.ID that doesnt have a status of A. But if the People.ID has a status of A to reject any other records for that People.ID.

So basicly I just need to select records with ReferID 5, and 6. 7 would be a duplicate of 6, so its useless. ReferID 4 is rejected since its status is A, 2 is rejected for the same reason. 1 and 3 are rejected because 2 is A since 1, 2 and 3 are all the same person.

Now how would I do this in a select statement?

thanks

 

[kenhamady]

You could do this in a SQL statement but that would be a different forum.

To do this in CR you need to do group selection. When you qualify one record based on a value in another, you have to use a subtotal.

Write a formula:
If Status = &quot;A&quot;
then 1
else 0

Group by PeopleID and sum the formula above for each group.

Select groups where the sum of the formula = 0
Hide the details and Group header, so you see only one record for each person. Sort the details so that the one you want to see (when there are multiples) is last.

Since CR has to select the subtotals Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com