Newbie Question regarding Reports with joins 1

[wakthar]

I have two tables called summary and turnover which has a many to one relationship.
The joins are based on region, contract, period and year.

In my report I have grouped by region and contract.
I am summing some fields and displaying them in the GF and RF of the report page.

My problem is I need to show another column from my turnover table to display the quarterturnover for region and contract for the period and year queried but since there is a many to one join, it seems to sum up the quarterturnover which is wrong.

I am thinking of removing the joins from the database links dialog and doing it separately in a formula but I dont know how!!!

Any help would be much appreciated!!

 

[lbass]

If there is only one value of quarter turnover per group and it is repeating, then use a maximum instead of a sum. If this doesn't work, please show sample data at the detail level.

-LB

 

[wakthar]

Sample data is as follows:

Table Contracts
id region contract period year dispute income
1 100 401 2 2006 200.00 100.00
2 100 501 2 2006 100.00 50.00
3 100 401 2 2006 100.00 50.00


Table Turnover
id region contract period year turnover
1 100 401 2 2006 500.00
2 100 501 2 2006 120.00


Report should be as follows:

Grouped by Region and Contract

PH Region Contact Dispute Income Total Turnover Exp
GF2 100 401 300.00 150.00 150.00 500.00 350
GF2 100 501 100.00 50.00 50.00 120.00 70

RF 300.00 150.00 450.00 620.00 280

I have problems working out Turnover since this refers to the turnover.Turnover field.

What I get is

PH Region Contact Dispute Income Total Turnover Exp
GF2 100 401 300.00 150.00 150.00 500.00 350
GF2 100 501 100.00 50.00 50.00 120.00 70

RF 300.00 150.00 450.00 1120.00 670

Turnover total is worked out by 500*2 + 120.

Please help!!

 

[dgillz]

Create a running total field, evaluate on change of group = contract, reset = never.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry