Crystal 9 Report - Multiple Table Problem

[Endeacott]

Hi,

In brief, I have two tables stored in Access with a One - many relationship.

LeadComp (LeadCompID, LeadCompName) one table
SubComp(SubCompID, LeadCompID, SubCompID) many table.

The main data on the report is from the Lead Comp table. At the end I have a total number of lead comps (running total) I also want to have a total of Sub Comps, but when I add this to my report the total of lead comps reduces.

A left outer produces just those in the SubComp table that match, but this then misses out the ones where a Lead Comp has more than one Sub Comp

I think it is to do with the relations but i cant get it to produce the right output.

I really would appriciate your help, if you need any more info please let me know.

Thanks
John

 

[lbass]

A left outer join should allow the display of all SubComps for each LeadComp, so make sure the link is from {LeadComp.LeadCompID} to {SubComp.LeadCompID} and that it is, in fact, a left join.

If your LeadComp running total is decreasing when you add {SubComp} to your report, it is probably because you have some LeadComps with no SubComps. (This is why you need the left join in the first place--left joins allow for absent data in the second table. If all LeadComps had SubComps, you could use an equal join and still return multiple SubComps per LeadComp.) To correct for this, replace {SubComp} with {@SubComp}:

If isnull({SubComp.LeadCompID}) then 0 else {SubComp.LeadCompID} //this assumes the ID is a number field. If it is a string, you could substitute "No SubComp" for the "0".

You can format {@SubComp} to suppress the 0 if you wish. This should allow a correct {#LeadComp}. Then create a running total on {@SubComp}.

-LB

 

[lbass]

On second thought, the second part of my previous post (the formula) is really unnecessary. I think your results have to be the result of an equal join. Can you explain how you are linking your tables, from what table to what table, and on what field? Can you verify that when you click on the table links that it says "left outer join"? The direction of the join (from what table to what table) matters, too.

-LB

 

[Endeacott]

Hi Lbass, Thanks for the response and sorry about the delay in replying.

I how noe sorted out the problem, I twas to do with the grouping of the report. I have four levels, Division, Region, Lead Comp and Sub Comp. The problem was I didn't realise that the Sub Comp needed to be a group its self. I have had to re-start the report and build up the levels of groups. This now provides an acurate count for lead comps and sub comps.

Thanks again,

John