how to get Sum of Field from left outer join table with condition

[AnandMishra]

Hi,

We have a table T1 for Order
Orderid Total other details
100 $100 shiptoname1
200 $200 shiptoname2
300 $300 shiptoname3
400 $100 Shiptoname4

We have a table T2 for Transaction
Orderid Amount trx type
100 $75 AUTH
100 $75 SETT
200 $100 AUTH
200 $100 DENI
200 $75 AUTH
200 $175 SETT

I am looking for some way so that i can bring in Order report the total Authorize amount(trx type = AUTH) for specific order.
eg:
for order id : 100
Data:
100 $100 shiptoname1 and $75(only AUTH amount)

and Order 200:
200 $200 shiptoname2 And 175(only AUTH amount)

and for 300:
300 $300 shiptoname3 And 0 (No record is there in T2 table)

and for 300:
400 $100 shiptoname4 And 0 (No record is there in T2 table)


I made left outer join between T1 and T2 and run the formula field in for "Amount" field of T2 table. PROBLEM is that it calcultes sum of all the matching record.

for Order 100:
Data:
100 $100 shiptoname1 and $150

and Order 200:
200 $200 shiptoname2 And $450

and if i go for inner join and condition for "trx type" field then i am not able to get order 300 & 400.

Any help is greatly appriciated.

Anand


Anand
anandm@entcomm.com

 

[kenhamady]

create a formula that says:

If {type} = "AUTH"
then {Amount}
else 0

Then total this field to get your conditional total. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[AnandMishra]

Thanks... It worked.

Anand
Anand
anandm@entcomm.com