Newbies First Problem

[Zedd]

I am a recent (2003) graduate from the local technical institute and have finally found my first IT gig. The company I am working for uses Access to import data from their AIMS database.
Anyway, I am trying to run a query (in access)that will filter out any item with a total positive Quantity On Hand in all locations. So I want all zero and negative inventory from all the locations (obviously to show up once). The problem I am having is that if there is a -10 in loc01 and +10 in loc02, my query brings this item back and I don't want it to because there is a positive QTY in loc02.
I am thinking that I have to loop through the locations looking for =>1 but I can't figure out how to hold on to the item once I have determined that none of the locations have any.

Does this make sense? I am still pretty green so I hope someone can help me out.

[taurus]

I hope I correctly understood your log.

Would a first query to isolate the zero and negative items and then use this result set in another query against the main table help?

I assume you have a table such as Stores (itemNo, locationNo, quantityOnHand).

The first query itemOutOfStock:

SELECT Stores.itemNo, Sum(Stores.quantityOnHand) AS totalQuantityOnHand
FROM Stores
GROUP BY Stores.itemNo
HAVING (((Sum(Stores.quantityOnHand))<=0));

and the second query locationOutofStock:

SELECT Stores.locationNo, Stores.itemNo, itemOutOfStock.totalQuantityOnHand
FROM Stores INNER JOIN itemOutOfStock ON Stores.itemNo = itemOutOfStock.itemNo;

could now be used as the base for the report data.

Hope this helps.

[Zedd]

SUM is what i am using now...the problem is that the sum of +10 and -10 brings back 0 and gets returned by the query.

i think i need to go through each item and discard the item altogether if there is a positive qtyOnHand.

Does that make sense?

[taurus]

Yes, modify the first filter query itemOutOfStock:

SELECT Stores.itemNo, Sum(Stores.quantityOnHand) AS totalQuantityOnHand, Sum(IIf([quantityOnHand]=0,0,1)) AS [check]
FROM Stores
GROUP BY Stores.itemNo
HAVING (((Sum(Stores.quantityOnHand))<0)) OR (((Sum(Stores.quantityOnHand))=0) AND ((Sum(IIf([quantityOnHand]=0,0,1)))=0));

and query locationOutOfStock:

SELECT Stores.locationNo, Stores.itemNo, Stores.quantityOnHand
FROM Stores INNER JOIN itemOutOfStock ON Stores.itemNo = itemOutOfStock.itemNo
GROUP BY Stores.locationNo, Stores.itemNo, Stores.quantityOnHand;

[Zedd]

Thx for your quick replies, I really appreciate it. Now I have to try and implement it. I do have a question tho...is that an If statement you've got embedded in there?
Thanx again

[taurus]

You're welcome. Yes, it is an immediate if (iif).