Gadi,

Things to try.

1. Make a small version of your tables with only a few records, and only the fields that you need. Run your SELECT on that and make sure you get exactly the results you want.
2. Using your existing tables, set a relation between JobLines and Paper and step through, browsing both tables and make sure there is always a Paper record matching your JobLines record.
3. Make sure SubAction and PaperKind are exactly the same. Do the LOOKUP() with PADR() and UPPER() if you need to.

>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.