Exception Report 1

[FireGeek21]

I need to do an "exception report" in which I have a PEOPLE table and a THINGS table. I am looking to return all PEOPLE that do not exist in the THINGS table at all and all PEOPLE that existed in the THINGS table but their records were stopped.

I can return all PEOPLE that have stopped records but am finding it difficult to return the PEOPLE that never had a record in the THINGS table.

Thoughts???

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[IdoMillet]

Use a SP, View, or Command with a UNION statement.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

What table tells you a record is stopped?

Use a left join FROM the People table TO the Things table.
You should be able to use a record selection formula like this if the things table holds the stop field:

isnull({things.ID}) or
not isnull({things.stop})

Don't know what your fields are, so if this response is insufficient, explain how you determine a record is "stopped".

-LB

 

[FireGeek21]

There are two fields START_DT and END_DT in the THINGS table. PEOPLE can have THINGS for a specific length of time. They can also have multiple different types of THINGS. So, if they still have a THING, the END_DT is empty or Date(1700, 01, 01). If they never had a THING, they don't have any entries in the THING table.

I will try your suggestion lbass and let you know the outcome.

Thanks

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)

 

[FireGeek21]

LB -

This solution works great. I guess sometimes I tend to venture down the "it must be a complicated solution" path vs the "keep it simple stupid" path!!

THANKS AGAIN!!!

FireGeek
(currently using Crystal Reports XI with Lawson 9.01)