Set theory (I'm not strong on it) but visualize your data as sets of dots with circles around them. The two circles overlap. On the left side is your master table, on the right side is your child table. A right join says, "Give me every child, matching parent where you can, and provide blank partents where you can't match" a left join says, "Give me every master and matching every child where you can, and giving me blank children where you can't match"

Maybe I didn't read the problem correctly, but I think you need a left join.

>Why Left Join? I need to pull the master table with the on hand table to see if each part number is on hand...but also there is another reason to use right join...and here's the crux of my problem: If a record for a part # doesn't exist in the Onhand table, then it should be assumed to have 0 on hand...how do I tell SQL to do that? The IIF statement didn't work, because SELECT couldn't find a matching part number in the on hand table to join on...argh!!! I feel as if it's all just within my grasp, but I'm missing something...HELP!!
>
>>I think you need a left join, instead.
>>
>>Your iif expression would be better as
>>IIF(EMPTY(INONHD.FONHND),'0',INONHD.FONHND) AS FONHND
>>
>>Good luck! (SQL has a lot of power, and you are going down the learning curve well. My hopes and wishes go with you!)
>>
>>>>>But now, if I search on a part in location 01 and it's there's no stock in location 01, in which case there wouldn't be an entry in the onhand table, how do I get it to select that part number and instead say that onhand=0?
>>>
>>>Thanks for your replies,
>>>
>>>The structure: InvMast is the Master table with the description, part number, stock re-order level, etc. InvOnHand is a table with only the part number, and the quantity on hand.
>>>In the InvOnHand table it creates a record for each part number in each location(ie.--if part 011 has 2500 in location 01 and 32 in location 02 then there will be 2 records, if however there is 3200 in location 02 and 0 in location 01 there will be only 1 record for the location that has something on hand).
>>>
>>>Thus, I can assume that when there is no record in the onhand table, that part number has 0 on hand in that location. My question is: How do I test for this within the SQL statement?
>>>
>>>Here again is the statement I have thus far:
>>>

SELECT INMAST.FPARTNO, INONHD.FONHND ;
>>>	FROM INONHD RIGHT JOIN INMAST;
>>>	ON INONHD.FPARTNO=INMAST.FPARTNO;
>>>	WHERE &LCWHERE;
>>>	INTO CURSOR TMP1

>>>LCWHERE is concatenated according to other conditions within the form...is there a way to put like an IIF() like this?
>>>

SELECT INMAST.FPARTNO,IIF(EMPTY(INONHD.FONHND),'0' AS FONHND,INONHD.FONHND)...

>>>I've tried the above and it keeps giving me an error when the SQL is executed that it needs a )....got any ideas?