cumulative total query

[Guest]

Hi there

I received some help on this awhile back but have just tried to put the suggestion into use and am sort of confused. Hope someone can help

What I am trying to do
create a report to tell user they can Issue parts if there is enough stock
I have a table ControlNoParts that stores the qty required per control no. there is an issue flag so I don't pick up qty req if it's already been issued
And I have an Inventory detail table which tells me qty in stock

I currently have qry that lets me know on a control no by control no basis if there is enough stock, but it does not take into consideration cumulative requrements. ie CN#1 needs 50 widgets and CN#2 needs 25. I have 55 in stock. My query would say both can be issued. But really only #1 can be because after #1 is issued only 5 are left which doesn't meet requirement for CN#2 of 25

I have been trying to figure out how to use the sub query for cumulative qtyreq then use that to compare to instock qty but I don't think I have the qty required right yet so don't want to go on

SELECT tblControlNoParts.ControlNo, tblControlNoParts.PartNo, tblControlNoParts.Qty, tblControlNoParts.Issue, tblControlNoParts.Qty AS CumQtyRe
FROM tblControlNoPart
WHERE (((tblControlNoParts.Issue)=False) AND ((tblControlNoParts.Qty)=(Select Sum (Qty) FROM [tblControlNoParts] as Temp WHERE [Temp].[ControlNo] <= [tblControlNoParts].[ControlNo] AND [Temp].[PartNo] = [tblControlNoParts].[PartNo] AND [Temp].[Issue] = False ))
ORDER BY tblControlNoParts.ControlNo

The main qry I'm just trying to pickup parts req from control no table if the parts haven't been issued. Then my subquery is where I'm trying to do cumulative req. but I don't seem to be getting good results
i.e. CN# 1 has Part THINGY qty 2 and Part WIDGET qty 1500. In the above qry it is only picking up Part WIDGET as being required. but I know looking at the table that both are still required, why is it not being picked up

sorry so long, but wanted to give all info I could.
thanks in advance..
Shawn

 

[Tom Ellison]

Dear Shawna:

The whole thing might be:

SELECT ControlNo, PartNo, Qty, Issue,
(SELECT SUM(Qty) FROM tblControlNoParts T1
WHERE T1.PartNo = T.PartNo AND T1.ControlNo <= T.ControlNo
AND T1.Issue = False)
AS CumQtyReq
FROM tblControlNoParts T
WHERE Issue = False
ORDER BY ControlNo;

Hope I've included all the parts you need.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts