>I have to tables:
>

>*Table1
>*fields:
>
>Id   ContrSum ContrDate
>--   -------- ---------
>1    100.00   2004/10/10
>2    200.00   2004/10/11
>
>...
>
>*Table2
>*fields:
>
>Id   PayedSum PayedDate
>--   -------- ---------
>1     50.00   2004/10/10
>1     20.00   2004/10/11
>1     10.00   2004/10/12
>1     20.00   2004/10/13
>2    200.00   2004/10/11
>

>
>I want the following table
>
>

>
>Id   ContrSum  ContrDate PayedSum PayedDate
>--   -------- ---------- -------- ---------
>1      100.00 2004/10/10    50.00 2004/10/10
>1        0.00               20.00 2004/10/11
>1        0.00               10.00 2004/10/12
>1        0.00               20.00 2004/10/13
>2       200.00 2004/10/11  200.00 2004/10/11
>
>

>
>Is that possible. The first table is master table & I right join table.
>
>
>TIA

hi Borislav,

I need a couple of Selects to do that

*---------------------------------------------------------------------------
function TestTable()

	create cursor Table1 ;
		(	Id		I	default 0, ;
			ContrSum	Y	default $0, ;
			ContrDate	D	default {} ;
		)
	
	Create cursor Table2 ;
		(	Id		I	default 0, ;
			PayedSum	Y	default $0, ;
			PayedDate	D	default {} ;
		)
		
	insert into Table1 values(1, $100, {^2004/10/10})
	insert into Table1 values(2, $200, {^2004/10/11})
	
	insert into Table2 Values(1, $50,  {^2004/10/10})
	insert into Table2 Values(1, $20,  {^2004/10/11})
	insert into Table2 Values(1, $10,  {^2004/10/12})
	insert into Table2 Values(1, $20,  {^2004/10/13})
	insert into Table2 Values(2, $200, {^2004/10/11})
	
	
	select	Table1.Id, ;
		ContrSum, ;
		ContrDate, ;
		PayedSum, ;
		PayedDate ;
		from Table1, Table2 ;
		into cursor	tmp1 ;
		where	( Table1.Id == Table2.Id) ;
		order by 1, 5
	
	select	Recno()	as	rec, ;
			* ;
		from tmp1 ;
		into cursor tmp2 ;
		order by 1
	
	select	Id, ;
		min(rec)		as	FirstRec, ;
		sum(PayedSum)	as	PayedTotal ;
		from tmp2 ;
		into cursor tmp3 ;
		group by 1
	

	select	tmp2.Id, ;
		iif(Rec == FirstRec, ContrSum, $0)	as	ContrSum, ;
		iif(rec == FirstRec, ContrDate, {})	as	ContrDate, ;
		iif(rec == FirstRec, PayedTotal, $0) as	PayedTotal, ;
		PayedSum, ;
		PayedDate ;
		from tmp2, tmp3 ;
		into cursor Result1 ;
		where	( tmp2.Id ==  tmp3.Id) ;
		order by rec
	
	
endfunc
*---------------------------------------------------------------------------