Re: Calculate Amount in stock

Tech-Archive recommends: Fix windows errors by optimizing your registry

Actually for .net it ended up looking like this

SELECT [AmountBought]-[Quantity] AS AmountInStock
FROM tblProduct LEFT JOIN tblSaleItems ON tblProduct.ProductID =
tblSaleItems.ProductID
WHERE (((tblProduct.ProductID)=7))
GROUP BY [AmountBought]-[Quantity];

But thanks for the push in the right direction.


CoachBarkerOJPW


"CoachBarkerOJPW" wrote:

Yes I noticed that and made the change.
Again
Thanks
CoachBarkerOJPW

"John Spencer" wrote:

If you are using .Net then the query may fail because of the use of the NZ
function. NZ converts Null to Zero so that you don't end up with a blank
for stock on hand where none of the item has been sold.

If you know that for every product there has been at least on sale of that
product, you should be able to change
NZ(Sum([QuantitySold]),0)
to
Sum([QuantitySold])
and get correct results

"CoachBarkerOJPW" <CoachBarkerOJPW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:50A579AC-6801-466E-B8BC-0C5EB0DB3AAF@xxxxxxxxxxxxxxxx
No it isn't homework, at least not for me. It was a group project and the
person who was in charge of inventory did not do their part. Now I have
about
4 hours to get inventory working, classes, data access classes and form so
it
can be presented to a customer tomorrow afternoon. This is in .net and
this
afternoon we have to turn the project into an executable. I was just
having
trouble with the query in the access database.

Also have to come up with a way to track sales tax quarterly for the
customer(same person was supposed to do this) based off of sales.

Just for the record the group is from Morrisville State College in upstate
NY and this project is for our capstone class. Out of 5 in the group only
1
came up short.

Thanks for the help.

CoachBarkerOJPW

"John Spencer" wrote:

You didn't say what problem you are having with the query. I am
guessing
that you are not seeing those products that have no sales. That could be
handled by changing the join to a LEFT Join instead of an INNER Join.
(Inner join means that records must exist in both tables)

Also, your table has QuantitySold but your SQL refers to Quantity
-- Since you are using aggregate functions, you must group by any field
that
you are not performing an aggregate on

SELECT tblProduct.ProductID
, Sum([AmountBought])- NZ(Sum([QuantitySold]),0) AS AmountInStock
FROM tblProduct LEFT JOIN tblSaleItems
ON tblProduct.ProductID = tblSaleItems.ProductID
GROUP BY tblProduct.ProductID

This looks like homework. If it is you just got a freebie answer

"CoachBarkerOJPW" <CoachBarkerOJPW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:C2F6A614-1EFC-438C-98DA-1921A55241B2@xxxxxxxxxxxxxxxx
I have two tables, one is tblProduct and the other is tblSaleItems.
tblProduct tblSaleItems
ProductID SaleItemID
ProductName DateOfSale
ProductCost Price
AmountBought QuantitySold
DateBought

What I want to do is calculate the AmountInStock by Subtracting the
QuantitySold from the AmountBought.

this is the sql so far
SELECT tblProduct.ProductID, tblProduct.AmountBought,
tblSaleItems.Quantity,
[AmountInStock]=Sum([AmountBought]-[Quantity]) AS Expr1
FROM tblProduct INNER JOIN tblSaleItems ON tblProduct.ProductID =
tblSaleItems.ProductID;

Any help would be appreciated.
Thanks
CoachBarkerOJPW






.