Gadi,

You originally used a LEFT JOIN from JobLines to Paper. Is there always a Paper.PaperKind = JobLines.SubAction?


>Hi Cindy,
>Thanks for your answer, but I can't seem to make the LOOKUP() work, it does not find the value, I get the empty(field size of Supplier) string back.
>Do I have to include the Paper.DBF in the SQL Query? I don't think so..
>Are there any more settings I need to check in order to make lookup work?
>
>Thanks,
>Gadi
>
>>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