Thanks Cindy,
I'll LOOK it UP !! :-)

Gadi Hutt


>Gadi,
>
>Try the LOOKUP() function to get the Paper.Supplier.
>

SELECT Joblines.action, Joblines.subaction, Jobsheet.jobsheet_no, ;
>	SUM(Joblines.TOTAL), ;
>	LOOKUP(Paper.Supplier, Joblines.SubAction, Paper.PaperKind, "PaperKind") AS Supplier ;
>	FROM  data1!Joblines ;
>	INNER JOIN data1!Jobsheet ;
>	ON  Jobsheet.jobsheet_no = Joblines.jobsheet_no ;
>	WHERE Jobsheet.job_stat = 3;
>	AND Joblines.action = "paper";
>	GROUP BY Joblines.subaction;
>	ORDER BY PAPER.supplier, Joblines.subaction

>
>LOOKUP() allows for the use of an index - I'm guessing that you would want to use the index on Paper.PaperKind. If not, take the reference to the index out of the LOOKUP() function.
>
>
>>Hi,
>>My application development is comming to a conclusion these days, I owe a great deal of that progress to this great forum and the people who are active in it. Thanks!!!
>>My application is a manging system for priniting shops and I need to print a report of "Paper Incomes" (I apologize about the spelling...)
>>My SQL statment is as follows:
>>
>>SELECT Joblines.action, Joblines.subaction, Jobsheet.jobsheet_no,;
>> sum(joblines.total), paper.supplier;
>> FROM data1!joblines LEFT OUTER JOIN data1!paper;
>> INNER JOIN data1!jobsheet ;
>> ON Jobsheet.jobsheet_no = Joblines.jobsheet_no ;
>> ON Joblines.subaction = Paper.paperkind;
>> WHERE Jobsheet.job_stat = 3;
>> AND Joblines.action = "paper";
>> GROUP BY Joblines.subaction;
>> ORDER BY paper.supplier, Joblines.subaction
>>
>>The reports lists the paper suppliers and their paperkind with the SUM of incomes from that paperkind.
>>Finnaly we come to the problem: I include the Paper.dbf because I take from i the Supplier name, but in that table there are a few lines for each paperkind name, because there are several Sheet sizes for each paperkind, and because of that I get a wrong SUM instead of SUM(group(joblines.total)) I get in the result set: SUM(group(joblines.total))*("number of paperkind entries in the PAPER.DBF")
>>All I need is to retrive the supplier name for each Joblines.subaction, but I don't want the multiple entries cause from the join with Paper.DBF
>>
>>Thanks in Advance, and soory for the length of my Q...
>>
>>Gadi Hutt