Yes there is, the value of the Joblines.Subaction is filled with Paper.Paperkind values, when the Joblines.Action = "Paper"...

Gadi

>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