Problem with getting values using Query

[manoj9849967222]

Hi All

I have a great problem.
I have three tables one is "SALES" other one is "Purchase" & "Productid"

Now i want to get the closing stock out of it.

Say productid is 1.2 --------- Purchased---100 qty & Sales-----50 qty
1.3----------Purcahsed---300 qty & sales-----100 qty
1.2--------- Purcahsed--000 qty & sales-----20 qty

closing stock

Productid Closing stock
1.2--------------------30 qty
1.3-------------------200 qty

I am trying to get the closing stock using a simple query. but the values i am getting is wrong.


I need to do this on a urgent basis.


Please Help

Regards
Manoj.

[mlcampeau]

You could try something like this:

[CODE=sql]SELECT [ProductID.Produ ctID], (Sum([Purchase.QtyPur chased])-Sum([Sales.QtySold])) AS ClosingStock
FROM [your 3 tables][/CODE]

If it doesn't work, please explain how it's not working, and let us know what you have tried.

[manoj9849967222]

HI

I have used these codes

SELECT productid.Produ ctid, (Sum([Purchase.Qty])-Sum([Sales.Qty])) AS ClosingStock
FROM product, sales, purchase;

when i run this query it ask me to enter the productid & gives me a wrong figure
I dont know from where i am getting that figure.

or other wise i was think to get the closing stock using a query which would give me the total qty sold & total qty purchased productid wise.

and in report creat a textbox & write a formula Qtypurchased-qtysold.

but when i do this also i am getting wrong values.

Regards
Manoj

[mlcampeau]

HI

I have used these codes

SELECT productid.Produ ctid, (Sum([Purchase.Qty])-Sum([Sales.Qty])) AS ClosingStock
FROM product, sales, purchase;

when i run this query it ask me to enter the productid & gives me a wrong figure
I dont know from where i am getting that figure.

or other wise i was think to get the closing stock using a query which would give me the total qty sold & total qty purchased productid wise.

and in report creat a textbox & write a formula Qtypurchased-qtysold.

but when i do this also i am getting wrong values.

Regards
Manoj

Well, if it is asking you to enter the ProductId, then you must not have it named correctly in your query. Try going to the Query Design view and select productid from the drop down field list. Also, in your From clause, your tables are not joined at all. In your query design view, make sure that the tables are joined by productid.

[mlcampeau]

Okay so I just did a test and this is how I had to get it to work. I made 3 queries.
The first sums the PurchasedQty:
[CODE=sql]SELECT Product.Product ID, Sum(Purchased.P urchasedQty) AS SumOfPurchasedQ ty
FROM Product LEFT JOIN Purchased ON Product.Product ID = Purchased.Produ ctID
GROUP BY Product.Product ID;[/CODE]

The second sums the SalesQty:
[CODE=sql]SELECT Product.Product ID, Sum(Sales.Sales Qty) AS SumOfSalesQty
FROM Product LEFT JOIN Sales ON Product.Product ID = Sales.ProductID
GROUP BY Product.Product ID;[/CODE]

And the third gives the results you want:
[CODE=sql]SELECT Product.Product ID, SumPurchased.Su mOfPurchasedQty-SumSales.SumOfS alesQty AS ClosingStock
FROM (Product INNER JOIN SumSales ON Product.Product ID = SumSales.Produc tID) INNER JOIN SumPurchased ON Product.Product ID = SumPurchased.Pr oductID;[/CODE]

[manoj9849967222]

Thanks for all your help.

I used the second option & it worked out


Thanks Again.

Regards
Manoj

[mlcampeau]

Your welcome. I'm glad it worked for you.