Inventory Query (trying to combine two rows in a sum query and union all)

[KyleAK]

I'm not entirely sure this is possible yet, but I am trying to create a
query to calculate inventory (this is going to be an automated data
transformation on a SQL server, so it would be great to have it in a
single query)...

My 'Inventory' table has the following columns:
InvtID (this is the item ID)
QtyOnHand
QtyCustOrd
QtyShipnotInv

My problem is that we have both a "raw" inventory ID and a "finished"
inventory ID that should be factored into the final "available"
inventory calculation for each item (in the end, we use only the
"finished" item IDs for sales and inventory use).

I was able to come up with the following query, however, I'm not sure
how to get a finished inventory ID in front of the total I have already
calculated (in this example, "FINISHED1" would need to be in front of
the total of the first statement before the UNION ALL, FINISHED2 in
front of the second total, etc):

SELECT Sum([QtyOnHand]-[QtyCustOrd]-[QtyShipNotInv]) AS QtyAvail
FROM Inventory
WHERE (((Inventory.InvtID)="RAW1" Or (Inventory.InvtID)="FINISHED1")
UNION ALL
SELECT Sum([QtyOnHand]-[QtyCustOrd]-[QtyShipNotInv]) AS QtyAvail
FROM Inventory
WHERE (((Inventory.InvtID)="RAW2" Or (Inventory.InvtID)="FINISHED2")
UNION ALL

Any help would be greatly appreciated! Thank you!

 

[Gary Walter]

just in case you have not already figured this out
(or my newsreader has not let me down
and did not download a previous response)....

it sounds like you are asking for:

SELECT
"FINISHED1" As InvID,
Sum([QtyOnHand]-[QtyCustOrd]-[QtyShipNotInv]) AS QtyAvail
FROM Inventory
WHERE (((Inventory.InvtID)="RAW1" Or (Inventory.InvtID)="FINISHED1")
UNION ALL
SELECT
"FINISHED2",
Sum([QtyOnHand]-[QtyCustOrd]-[QtyShipNotInv])
FROM Inventory
WHERE (((Inventory.InvtID)="RAW2" Or (Inventory.InvtID)="FINISHED2")
UNION ALL
.....

note: in a UNION query, the first query
determines the name of the returned fields
and any further aliasing is not needed...