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