Hi Peter,

----------
now in the query if a claim hase for example 3 claimants I ned to have 3 lines with just the claimant info different
if there are alose 3 carriers I need 3 more line containing the same claim info but with the 3 carrier names etc
What I would like the best wourd be a treeview like grid

here is part of the query method:

SELECT &cFieldList,0000000 AS LINENO;
FROM claims ;
   LEFT JOIN links la ON la.parent_id=claim_id AND la.ltype="CLRE";
   LEFT JOIN links lb ON lb.parent_id=claim_id AND lb.ltype="CLCA";
   LEFT JOIN links lc ON lc.parent_id=claim_id AND lc.ltype="CLCT";
   LEFT JOIN respond ON la.child_id=respond_id;
	   LEFT JOIN carrier ON lb.child_id=carrier_id;
	   LEFT JOIN claimnt ON lc.child_id=claimnt.claimnt_id;
	   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
		LEFT JOIN statute ON lc.statute_id=statute.statute_id;
	WHERE &lcWhere ;
INTO CURSOR cc1 ;
READWRITE

------------------

Your request is a union; a multi autojoin is very inefficient,
and the result it depends by &cFieldList and &lcWhere, and i think you must add
a grouping.

try ( VFP not support complex subquery ) with a
SELECT for "CLRE" and ( on VFP8 ) INSERT the rows for "CLCA" and "CLCT":

SELECT &cFieldList,0000000 AS LINENO;
FROM claims ;
   LEFT JOIN links la ON la.parent_id=claim_id AND la.ltype="CLRE";
   LEFT JOIN respond ON la.child_id=respond_id;
   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
WHERE &lcWhere1 ;
INTO CURSOR cc1 ;
READWRITE

INSERT INTO cc1 ;
SELECT &cFieldList2,0000000 AS LINENO;
FROM claims ;
   LEFT JOIN links lb ON lb.parent_id=claim_id AND lb.ltype="CLCA";
	   LEFT JOIN carrier ON lb.child_id=carrier_id;
	   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
	WHERE &lcWhere2

INSERT INTO cc1 ;
SELECT &cFieldList3,0000000 AS LINENO;
FROM claims ;
   LEFT JOIN links lc ON lc.parent_id=claim_id AND lc.ltype="CLCT";
	   LEFT JOIN claimnt ON lc.child_id=claimnt.claimnt_id;
	   LEFT JOIN sys_user ON claims.analyst_id=sys_user.user_id;
		LEFT JOIN statute ON lc.statute_id=statute.statute_id;
	WHERE &lcWhere3

INDEX on claim_id is not useful for the left join
INDEX on links , child_id , user_id, statute_id can speed up a lot.

Fabio