two lines but only want the last line in my purchase order (1 Viewer)

[rainbows]

the top fo0rm shows the items that need purchasing the second form shows me the situation with stock etc and what allocation but not allocated . when the first line for that part is allocated and i tick the box it will disapear from the form. as you can see there are 2 transactions for that part f which i only want the second on to show up in my purchase order form and the first transaction could have been for order a week ago but has not been allocated yet so it it showing history

how can i get it to tell me the outstanding qty is 89 and not show me the 86

thanks steve

INSERT INTO [supplier orders] ( [stock number], material, costs, type, unit, [Qty required], balance, [qty outstandng] )
SELECT [Order Details order suppliers3].StockNumber, [Order Details order suppliers3].Material, [Order Details order suppliers3].Cost, [Order Details order suppliers3].Type, [Order Details order suppliers3].Unit, [Order Details order suppliers3].ReqQty1, [Order Details order suppliers3].BALANCE, IIf([balance] Is Null,Sum([reqqty1]),Sum([reqqty1]-[balance])) AS outstandingqty
FROM [Order Details order suppliers3]
GROUP BY [Order Details order suppliers3].StockNumber, [Order Details order suppliers3].Material, [Order Details order suppliers3].Cost, [Order Details order suppliers3].Type, [Order Details order suppliers3].Unit, [Order Details order suppliers3].ReqQty1, [Order Details order suppliers3].BALANCE, [Order Details order suppliers3].qtyoutstanding, Now()+91
HAVING ((([Order Details order suppliers3].Type)<>"capacitor"))
ORDER BY [Order Details order suppliers3].StockNumber, [Order Details order suppliers3].Type;

 

[Pat Hartman]

Then summarize the data in the query by eliminating the batch number. Then you will append 1 record with a quantity of 5

 

[rainbows]

at this time the batch number is advising purchasing/stores what allocation qty has been allocated to the batch number/part assembly number

therefore when we checked the parts for PCO 00011 the query would say be did not need to order that part of 6 because we had 10 in stock
then we got another order for PCO00012 for 3 and again it says we have 4 in stock so still dont need to order
but now i have an order for 90 and the query runs and i was hoping it would only see the record with 1 in it stock /balance and tell me to order 89 to fulfill the order for 90 , i have been working on this for weeks trying to get this to work and i thought i had until this problem happend

steve

 

[Pat Hartman]

If you want the batch number, you get one record per batch number! You could concatenate them but if you want to know the amount per batch, that doesn't work.

Are you trying to do two things with one query? Build a second query, they're cheap

 

[CarlettoFed]

If you attach a sample file it would be easier to help you.

 

[rainbows]

i have done this hopefully to overcome the problem if i tick the line complete tick box to say the information is recorded and available to see until the parts for that line has been actually allocated then when i tick the allocated tick box it will not be seen again
i added the "line complete" to my query and stated "false"

thank you for you help .

steve