Outer Join?

[DanCCC]

I'll try to be succinct here.

I work in a department tracking Donors and Gifts (donations).

I have a table of Donors, who are linked to a table of Gifts. Those Gifts are given to various Accounts.

These Donors are assigned to Caseloads via a unique number.

I represent a subset of a large organization, so one of my reports (we'll call it Report #1) shows Donors who have given to the Accounts in which I'm interested, and their Gifts. Easy.

I also easily created a report (#2), selecting by Caseload, listing all the Donors, whether or not they've given.

Here's the tricky part: I need to create a report, showing all Donors belonging to a Caseload, and their Gifts to OUR Accounts only, as well as the Donors who have *Never* given to these Accounts (they may have given to other Accounts).

If I use the selection criteria of Report #1, only the Donors who actually gave a Gift show up. (And I want to see them all.)

If I use the selection criteria of Report #2, I see all the Donors belonging to the Caseload, and I see all their gifts to ALL the Accounts. But I only want to see the gifts to our Accounts.

Someone told me I need to create on Outer Join. Can anyone show me how to pull this off?

Thanks,

Dan Lee

 

[Mannga]

In Crystal Reports... click on the Table Linking Icon.

then click on the link between your two tables. Then click on Link properties.

it's in there.

 

[DanCCC]

Mannga,

Thanks - I am now looking at the "Visual Linking Expert" with a thing like this (I've re-named the tables to use the terms I used before):

DONORS GIFTS
xxx ---> xxx
xxx xxx
xxx xxx

I right-click on this link and chose Right outer join - [(+)=,=*], and it returned exactly the same records. I tried the left join, same thing.

Having changed the link, do I also need to change my selections then?

Thanks,

Dan

 

[IdoMillet]

The Outer Join results in donors with no donations having Null account information.
Those records then fail the account selection criterion.

You can use a subreport to return only the non-donors or use a UNION to combine donors and non-donors.

There's a 3rd, potentially simpler, approach but I'm not 100% sure it would work. E-mail me (ixm7@psu.edu) if you want to test it.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com