This is fairly complex (to me), and I probably won't explain it right, but here goes.
I have two tables, Table1 and Table2, a one to many relationship. Table1 contains JobNumber (the relationship field) and JobRep. Table2 contains Portal1, multiple instances of ItemPrice, and TotalPrice, a summary of those prices. TotalPrice is multiplied by field Commission to return TotalCommission. Table2 also contains Portal2, OutsidePayments, and TotalPayments, a summary of those payments. Calculation field Profit = TotalCommission - TotalPayments. I want to run a columnar report sorted by JobRep with Subsummaries and Grand Totals of TotalCommission and Profit. But when I run the report, only the value of the last record is given for the subsummaries and grand totals. For example:
JobNumber 1, JobRep Bob
ItemPrice 500
ItemPrice 500
TotalPrice 1000
Commission 5%
TotalCommission 50
OutsidePayment 20
OutsidePayment 5
TotalPayments 25
Profit 25
JobNumber 2, JobRep Bob
ItemPrice 1000
ItemPrice 1000
TotalPrice 2000
Commission 5%
TotalCommission 100
OutsidePayment 10
OutsidePayment 10
TotalPayments 20
Profit 80
JobNumber 3, JobRep Jim
ItemPrice 100
ItemPrice 100
TotalPrice 200
Commission 5%
TotalCommission 10
Outside Payment 5
TotalPayments 5
Profit 5
JobNumber 4, JobRep Jim
ItemPrice 100
ItemPrice 50
TotalPrice 150
Commission 10%
TotalCommission 15
OutsidePayment 5
OutsidePayment 5
TotalPayments 10
Profit 5
Returns report:
Bob
JobNumber 1 TotalCommission 50 Profit 25
JobNumber 2 TotalCommission 100 Profit 80
Bob 100 80
Jim
JobNumber 3 TotalCommission 10 Profit 5
JobNumber 4 TotalCommission 15 Profit 5
Jim 15 5
---------------
15 5
I've tried every combination of leaving running total unchecked and checked, and leaving restart summary for each sorted group checked and unchecked, and nothing changes the result. I've searched Google and this forum, but I can't find anything about this. Can anyone help me?
