BorlandTalk.com

Guest · Posted: Sat Dec 09, 2006 2:11 am Post subject: Update A table with another.

Hi

I use ado and connect mdb file thorough ms jet engine

I have two table

T1 have 4 fields
1 : Id : Int
2: name : str
3: sum1 : double
4:sum2 : double

T2 have 3 fields
1: T1id : int
2:Amont : double
3: tType : int

the records on t1 are like

1 Record1 10.00 5.00
2 Record2 3.00 2.00
.....

the records on t2 are like
1 12.00 1
1 8.00 1
1 5.00 2
2 5.00 1
....

what i want to do within one sql sentence
sum T2 group by T1id where ttype = 1 and write to T1's sum1
sum T2 group by T1id where ttype = 2 and write to T2's sum2
do this for each record on T1, if not exists on t2 set Sum1 or/and Sum2 to
zero

best regards