Not showing all records

[bkatzman]

Greetings,

I am doing some testing in Crystal Reports XI with only 5 records that I pulled from an .mdb file (that was exported from Raiser's Edge).

There are three table: BIO, GIFT, GIFT NOTES,
which outputs: First Name, Last Name, Gift Amount, Gift Balance, and 1 gift note

Currently it will only output 2 out of the 5 records, because two of the records actually have gift notes. I have played around with the table linking, but obviously have not gotten it right because it either displays 2 out of the 5 records, or all 5 but duplicates notes for the records that do not have notes.

Please help and thank you very much!
Bryce

[bkatzman]

Here is a doc with a screen shot of the tables

[jade1977]

It is because you have none of your tables linked to each other.

[bkatzman]

Sorry about that. I didn't want to front-load people by showing the links that I used. Here they are.

Thanks!

[jade1977]

I'm not sure what you mean by "front load people" but need the entire picture to really help! Anyway, it still sounds like a linking issue. Either one of the tables are linked incorrect, or you need to place with the joins. http://en.wikipedia.org/wiki/Join_%28SQL%29

[bkatzman]

By front-load, I meant I didn't want to show the links that I used so that people didn't think they were correct.

Above the second attachment shows the linking that I used.

At first I thought it was the linking, but I can't see how that would result in records that are missing data in one or more of the fields would prevent it from displaying...

Thanks for looking into this for me!
Bryce

[jade1977]

It could prevent it, if the links are Inner Join and there are not exact matches for both tables linked then it will not display. Try chaning your link option to left outer or right outer joins. I think that would solve your issue. Also looking at your links, it appears that one of the tables are not linked correctly. Try posting a sreen shot of the Order Links window.

[bkatzman]

I tried the outer links (starting the link from gifts to bio first).

Attached is the order links

[bkatzman]

I FINALLY figured it out. You're advice that about Outer Joins put me on the long trail of figuring out my original issue of records not showing if they did not have data in one of the tables.

To solve this:

Once I linked the BIO>GIFT>GIFT NOTES TABLE,
I went into Database>Database Expert>Links
I highlighted the link between the Gift and Gift Notes table (which is the table that did not have data in it for every record) and selected "Link Options" and chose "Left Outer Join" and "Enforced To". The result was every record was displayed even those that did not have data in the GIFT NOTES table.

Thanks so very much for your help!
Bryce