Background - I have removed all the extra fields giving only needed items. In reality there are 4 PO types,2 stores

We have 2 Tables

**Inventory**( INVENTORY )

inv_filecode_short

inv_part_number

inv_part_description

inv_current_inventory

inv_reorder_qty

**PO**( PURCHASE ORDERS )

po_inv_filecode_short

po_part_number

po_description

po_type (can be either 'STK' or 'BO'

po_store1_qty_order

Using this SQL statement - I am able to generate are report that shows all items on order. (This gives me calculated field reflecting the qty on order STK and BO. The reason I am using the Sum function is to take into account the fact that an item can be on multiple POs.

Select IT.inv_filecode_short,IT.inv_part_number,IT.inv_part_description,IT.inv_current_inventory,IT.inv_reorder_qty,

SUM(DISTINCT CASE WHEN PT.po_type='STK' and .po_inv_filecode_short=IT.inv_filecode_short THEN PT.po_store1_qty_order ELSE 0 END) AS sum_store1_qty_order_stock,

SUM(DISTINCT CASE WHEN PT.po_type='BO' and PT.po_inv_filecode_short=IT.inv_filecode_short THEN PT.po_store1_qty_order ELSE 0 END) AS sum_store1_qty_order_bo

FROM INVENTORY AS IT PO AS PT

INNER JOIN PO ON IT.inv_filecode_short=PT.po_aw_filecode_short

GROUP BY IT.inv_part_number"

However what I need to do is create a reorder query reflecting the current inventory level vs the reorder point and then automatically factor any item that are on STK orders (the BO Items are not factored in the reorder calculation -- remember this is an short version of what I need There are actually 4 PO Types !! Plus Transfers,Customer Commits )

The reorder calculation would be similiar to this.

reorder_qty=(inv_reorder_point-inv_current_inventory)-STK_QTY_ORDER. Creating an recordset with a calculated Field would be ideal. This will allow me generate 'Working' POs

Plus limit the returned recordset to only items that have a reorder qty >0

Of course this is for the complete Inventory file.

I hope this explains the situation I am in. Any help or suggestions would be greatly appreciated.