You will need 2 SELECT to do what you want:

SELECT DISTINCT InOnHd.PartNo;
   FROM InOnHd
   INTO CURSOR Temp1

SELECT Temp1.PartNo, Location.Location, IIF(IsNull(InOnHd.OnHand), 0, InOnHd.OnHand) AS OnHand;
   FROM Location, Temp1;
   LEFT OUTER JOIN InOnHd ON (Temp1.PartNo = InOnHd.PartNo) AND (Location.Location = InOnHd.Location);
   INTO CURSOR Temp2

If you want only those with OnHand = 0:

SELECT *;
   FROM Temp2;
   WHERE OnHand = 0

As you probably noticed, I assume that you have a table named Location that contain all the possible locations.

HTH

>

INMAST.DBF
>===============
>PART# DESCRIPTION TOTAL_ONHAND  REORDER_AMT    GROUP
>----- ----------- ------------  -----------    -------
>0001   LED DISP.     450.00         600.00     CURRENT
>0002   CIRCUIT BRD   150.00         300.00     CURRENT
>0003   TRASH BAG      30.00          10.00     SUPPLIES
>
>INONHD.DBF
>===============
>PART#   ONHAND   LOCATION
>-----   ------   --------
>0001    200.00      01
>0001    250.00      02
>0002    150.00      02
>0003     30.00      01

>
>There ya have it, actually, these tables are not one that I maintain, but are built and maintained by another program, but that other program won't do what we need it to do, so I'm extracting the info. I just found the TOTAL_ONHAND field in the master table yesterday(alot of fields in there). The main problem is as you can see in the INONHD.DBF sometimes we have parts that are in location 01 AND 02, but there are many cases where a part will be in only 1 location, so that means that there is an inventory of 0 in location 01; 01 is the stock room, 02 is the factory, thus parts kinda flow from 01 to 02, but we need to know when 01 is out of something. So there's my problem, how they heck to I do a SELECT but include ALL records from the INMAST and report an ONHAND of 0. Is there a better way? Share with me your wisdom oh swammi ;P