Sorry, I should have looked more closely at what I was doing.
There are TWO locations, and that's it right? Then you should be able to do this with a union clause. Use the original select that we were talking about a while ago, but add records to it for the missing locations.
>While your suggestion does get me all the parts, if I filter on location 01, I still don't get those parts that don't have an entry for location 02...like part# 0002 below, I need to show on hand of 0 for that part# when filtering on location 01....ARGH!!! :) Thanks for your continued help.:)
>
>>Select location from inonhd group by location into cursor locations
>>select * from inmast, inonhd, locations where inmast.partno = inonhd.partno into cursor allstuff
>>
>>That will join every location with every part.
>>
>>>
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
--Todd Sherman
-Wake Up! Smell the Coffee!