Running Totals on Multiple Tables without Subreport

[random621]

I am running Crystal Reports XI on Windows XP Pro against an Access database.

I have a report that involves for tables: Commodity, CashContracts, HedgedContracts, and ReOwnedContracts. Each of the contracts table is linked to the Commodity table via Commodity.CommYearID. Also in each Contract table there is a field called "PercentSold" (per contract).

I am trying to create a report that will display the total percent sold for each of the 3 contract types per commodity.

In the past I would have always used subreports to accomplish this, but this time I would like to try using Variables or some other method so that I can do the totals without subreports. Is this possible and can anyone give me points on how to go about it?

My problem is I always end up with totals that are too high because of duplicating rows (which is why I used subreports in the past).

 

[lbass]

Are you using the exact same fields from each contract table for the purposes of this report? It might make sense to use a command as your datasource where you use a union statement to essentially merge the fields from the contracts into one field. Something like this:

Select 'CashContracts' as Type, Commodity.`ContractNo`, CashContracts.`ContractNo` as ContractsContractNo, CashContracts.`PercentSold`
From Commodity Left Outer Join CashContracts on
Commodity.`ContractNo`= CashContracts.`ContractNo`
Union All
Select 'HedgedContracts' as Type, Commodity.`ContractNo`, HedgedContracts.`ContractNo`, HedgedContracts.`PercentSold`
From Commodity Left Outer Join HedgedContracts on
Commodity.`ContractNo`= HedgedContracts.`ContractNo`
Union All
Select 'ReOwnedContracts' as Type, Commodity.`ContractNo`, ReownedContracts.`ContractNo`, ReownedContracts.`PercentSold`
From Commodity Left Outer Join ReownedContracts on
Commodity.`ContractNo`= ReownedContracts.`ContractNo`

Then you could insert a group on {command.type} and do summaries per group. Not quite sure what summaries you are looking for or what fields you might need to incorporate.

-LB