join thr unique field & non-uniuqe field 1

[13sio]

CR6/Sybase Anywhere 5.5/ODBC

i have 2 tables
Table_A w/ fields: transaction_id, cost
Table_B w/ fields: how_pay_id, transaction_id, pay_method, receive_place

i wanna obtain the sum of Table_A.cost group by Table_B.receive_place.

i make TableB_receive_place as a group, and insert a formula @sum(Table_A.cost, Table_B.receive_place) when Table_A.transaction_id=Table_B.transaction_id. However, i get wrong summation due to repeated Table_B.transaction_id.

If a customer pays a transaction by 2 methods, there are 2 records w/ the same transaction_id in Table_B. No matter how many types of pay_method, a transaction has only one receive_place. So, Table_A.cost need be added related to arbitrary Table_B.pay_method and ignore the rest.

i have no idea to do that and need your helps.

Thank you in advance.

 

[synapsevampire]

Group by Trans, and then by Receive.

In the Trans header place:

whileprintingrecords;
numbervar MyTot :=0

In the Receive place
numbervar MyTot :=MyTot+{table.cost}

Display in the Trans Footer using:

whileprintingrecords;
numbervar MyTot

Hope this gets you closer.

-k

 

[13sio]

Thank you very much. i follow your guideline and make some modification as follows,

In the Trans header place:

whileprintingrecords;
numbervar RunningTotal:=0

In the Receive place

whileprintingrecords;
NumberVar RunningTotal;
If not onlastrecord Then
  If ({Howpaid.TRANSACT} = Next({Howpaid.TRANSACT})) Then 
    RunningTotal
  Else  RunningTotal:= RunningTotal + {POSHEADER.TAX1}
Else RunningTotal:= RunningTotal + {POSHEADER.TAX1};

Display in the Trans Footer using:

whileprintingrecords;
numbervar RunningTotal;