Duplicate records with left outer join

[Chasidy]

I am joining three tables. One is folder joined to gift by folderID (one to many relationship) and folder is joined to pledge by folderID (also one to many relationship). The problem is when the records are retrieved, I receive duplicate records to &quot;Fill space&quot; (ie. I have 5 gifts but only 2 pledges, the pledges will be repeated to fill space.) I want to sum the gifs and sum the pledges independently. I have tried formulas and running totals. The problem is if you sort pledges to use a {pledge.pledgeid} <> previous({pledge.pledgeID}) then the gifts formula will not catch duplicates because Crystal repeats the gifts in order. Then if I sort on giftID, the gifts will work but the pledge formula doesn't work. I have also tried to select distinct records but it doesn't catch the duplicates I am looking for.
I am using ADO and Crystal 9 professional.
Any help / advice would be GREATLY appreciated!!!!
ps. I have created similar reports in ODBC where you can solve the problem by using a UNION query but I can't seem to get a query to work in ADO (though CR 9 seems to say you can.)

 

[HowardHammerman]

I assume that you can't link pledge to gift. The best way around this is to use a sub-report. In the main report link Folder to Gift as you currently do. Now create a sub-report containing only the Pledge table. Place the sub-report in a group header or group footer of the main report (group on FolderID). In the sub-report supress all sections except the detail section. In the detail section, place the info you need about pledges. If you need to comibine info from the two reports, you will need to create formulas with shared variables. Check out George Peck's &quot;Complete Reference&quot; book for some good examples of this kind of report. Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Chasidy]

No, you cannot link gift to pledge. The joins need to be two left outer joins one from folder to gift and one from folder to pledge. This report I am working around has been around since the beginning of time and has &quot;All&quot; the information our customers want...but with the change-over from DAO to ADO, I cannot do what want to see with subreports. If there is a way to not do subreports that would be the preferred option. I will look for the book you recommended and see if there are any other helpful tips in there. Thank you for your quick and helpful reply though.

 

[Chasidy]

Is there any way to use queries (specifically UNION queries) with ADO? I use UNION queries with our ODBC side and the problem is eliminated (though not efficient). I tried using the Query Designer but my only data source options are ODBC.]

 

[HowardHammerman]

I am not sure if ADO supports Union queries. I guess you can try and see. What about the sub-report route? Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Chasidy]

I am working on using the subreports right now. I did Gift in the main report and the Pledge in the subreport. I am relying heavily on the subFormula / mainFormula that is listed on this message board. It is a slow process but I think it might be working. Right now I am trying to determine how to do a summary of the mainFormula.