Exception Report 2

[FireGeek21]

I am looking to do an exception report. Need to know who doesn't have a specific item in a left outer join table. Example:

Person 1 can have apples, bananas, and grapes.
Person 2 can have peaches and bananas
Person 3 can have bananas, grapes and peaches.

I want a list that can return a list of people that do not have grapes. Person 2 should show on that list.

All the fruit is stored in the FRUIT field of the FOOD table. The person id is in the FOOD table.

I was thinking of doing something like grabbing a list of people from the PEOPLE table and somehow doing a SQL Expression of all the people in the FOOD table with grapes. Then in the Report Selection say something like NOT[PEOPLE.personid in {@Grapes_SQL}].

Am I on the right track? If so, stumped on the SQL Expression...

Thanks!

FireGeek

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[lbass]

Insert a group on People.ID. Create a formula like this:

//{@hasfood}:
if {table.food} = {?Food} //or "Grapes"
then 1

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

sum({@hasfood},{people.ID})=0

-LB

 

[hilfy]

Try this:

1. Inner join from Fruit to Food then Left join FROM Food TO People.
2. In your Select Expert use this logic:

{Fruit.FruitName} = 'Grapes' and
IsNull({People.PersonID})

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[FireGeek21]

Hilfy,

I have only two tables PEOPLE and FOOD. I was doing a left outer join from PEOPLE to FOOD because PEOPLE can have multiple records in the FOOD table.

Working on LBass' suggestion now. Will report back. Thank you!!!

FireGeek

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[hilfy]

But the only way to get all of the food that doesn't have a person is to go from Food to People.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[hilfy]

I'll take that back...I can think of a way to do that joining in the other direction.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[FireGeek21]

I want all the people that do not have a specific type of food. It is an audit to see if we missed entering something.

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

LBass your suggestion worked like a charm and we actually found some data we need to clean up!

As always ~ THANK YOU!

FireGeek21

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)