Cumulative Qty

[Guest]

Hi there

I have a query with a sub query that calculates the cumulative Qty req'd based on an orders type table.
I use this qry as a base to figure out stock shortages, so using the Items Detail tbl Qty - the Cumulative value based on my above qry I can figure out what 'orders' I can fill and what I need to order. I'm not sure how or if this can be done

The qry is working fine except the way the 'orders' work is that you can't fill an order unless all parts on that order are in stock. So what I need to figure out is this - if a part can't be used on this Control Number right now because it's waiting for other parts how can I have the cumulative qry i.e. 'release' that qty so it might be used on a Control Number further down the list where all the other parts are in stock -

I don't know if I'm wording my question well so I'll give an example..

CN#
parts req Qty On Hand Cumulative - Stock Shortag
Thingy 20 3 - 1
Widget 10 25 15

CN #2
Thingy 2 -1
Widgets 10

What I would like to have happen is since On CN 1 there isn't enough stock to release the whole order, let the 3 on hand move to the next CN to see if it can be release - in this case we do have enough physical stock but my qry says there is a shortage

Please help me....I got this far and it's like I keep taking a baby step forward and then 2 back. ahhhh
Thanks so much in advance with this. If a sample of my qry is req'd please let me kno
Shawn

 

[Guest]

I am going to try it right now, thank you so much. I think I have the result I'm looking for but it took 5 queries to get it so I'm going to try your idea now

Thank you agai
Shawna

 

[Guest]

Hi there

I went tried to replicate your suggestion but I had to leave out one line where you had subcontrolnoparts.cn = subitemdetails.cn because I don't store the cn in itemdetails (until after pts are issued for that cn) - item details is just that i write to it when parts are recieve and parts are issued and use those qty's to calculate on-hand. So my qry looks like this

SELECT qrySumCNParts.ControlNo, qrySumCNParts.PartNo, qrySumCNParts.[Sum of Qty] AS QtyReq, qrySumItemDetails.SumOfQty AS OnHand, (SELECT [qrySumItemDetails].[SumOfQty] - [qrySumCNParts].[Sum of Qty] FROM qrySumCNParts AS SubControlNoParts INNER JOIN qrySumItemDetails As SubItemDetails ON (SubControlNoParts.PartNo = SubItemDetails.ItemNo) WHERE SubControlNoParts.ControlNo < qrySumCNParts.ControlNo AND SubControlNoParts.PartNo = qrySumCNParts.PartNo)-[qrySumCNParts].[Sum of Qty]+[qrySumItemDetails].[SumOfQty] AS Shortag
FROM qrySumCNParts INNER JOIN qrySumItemDetails ON qrySumCNParts.PartNo = qrySumItemDetails.ItemN
WHERE (((qrySumCNParts.Issue)=False))

I've used sum qry's to sum the parts req for a cn (the same part might be added twice - I just need total) and sum the qty in item details to determine on hand

When I run the qry I receive an error that this sub-query can 'return at most one record', I say ok and it returns one record with all fields having Name# in them. ??? Any ideas - is it because I'm summing

Thanks Shawn