I have an SQL Query challenge.
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.
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.
