Do you have a third table that identifies all of your part numbers and what they relate to?  If you have that, this is not difficult.  If you don't have that, you're going to have to write a "Command" which is a SQL Select statement instead of selecting tables and having Crystal create the SQL for you.

(not IsNull({PurchaseOrders.PartNumber}) or not IsNull({WorkOrders.PartNumber)) and
(IsNull({PurchaseOrders.PartNumber}) or {PurchaseOrders.OpenOrder}= 1) and
(IsNull({WorkOrders.PartNumber}) or {WorkOrders.OpenOrder} = 1)

hello and thank you for your reply.  what i want to do is read all the records, both work orders and purchase orders, as if they are one file.  in other words, i use the part to link to each file, but i would like to somehow output them to a temporary file, where i could then sort both files together by due date.    Is this possible ?

I did read about Union queries,  but according to what I read, both tables must have the same table structure.  you are not able to perform a union on two tables with different fields.   These tables both have a common field  (part number)  but I want to look at P.O. # in the purchase order table, and Work Order # in the job file and use this as one field, also qty on order/qty. committed and due dates.   They do not have the same table structures.  

Because you just need the starting inventory, this is going to be a little complicated, but it is doable.

Hello, yes I'm looking to have a running total on each line.   for the first record I want to add or subtract to the beginning qty on hand, and then on the next line I want to add or subtract from the result of the previous record, and then continue the same on the next line... for example:

sorry...the qty on hand field is called QTY_ONHAND

thanks!

Select 
  'P' as TABLETYPE, 
  po.PART, 
  po.PURCHASE_ORDER as ORDERNO, 
  po.DATE_DUE_LINE as ORDERDATE,
  po.QTY_ORDER as QTY,
  inv.QTY_ONHAND
from V_PO_LINES as po
  inner join V_INVENTORY_MSTR as inv on po.PART = inv.PART

Union

Select 
  'W' as TABLETYPE,
  jc.PART,
  jc.JOB as OrderNumber,
  jc.DATE_DUE as ORDERDATE,
  jc.QTY_COMMITTED as QTY,
  inv.QTY_ONHAND
from V_JOB_COMMITMENTS
  inner join V_INVENTORY_MSTR as inv on po.PART = inv.PART

I am so CLOSE now to what I want.....I have the running total adding or subtracting as it should.  The only problem now I'm getting is on the first record I want the balance of on-hand +/- qty.    Right now this is what I'm getting:

Qty. On Hand   1,421