Query Prob....... KRISH

[KRISH]

Hi! everybody,
Could anybody help me in solving the following prob.
I had three tables
[P_list] -fields are [PrID], [Pname]
[Store] - fields are [StoreID],[PrID],[Qnty],[RecvDt]
[StockOUT]- fields are [StoreID],[Qnty],[IssueDt]

Problem to solve: I want to find out current Stock available in store, which
should contain all products available in [store] table, even though they were
not issued.

I tried with the following query
SELECT store.prid, p_list.pname, p_list.ptype,
Sum(store.qnty)-nz(Sum(stockout.qnty)) AS curstock
FROM p_list INNER JOIN (store LEFT JOIN stockout ON store.storeid =
stockout.storeid) ON p_list.prid = store.prid
GROUP BY store.prid, p_list.pname, p_list.ptype;

Problem with the above query is
(i) if some product having [storeid] issued more than once in [stockout]
table then Sum(store.qnty) is giving result of sum of stock multiplied by no.
of times it is issued, which is absolutely wrong.

(ii) if i give [issuedt] criteria then my query is ignoing the products
which received but not issued.

Kindly let me know how to solve the problem. Any help is greatly appreciable.
Thanks a lot in advance.
Krish

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

This might do it (for Access 2002 & higher):

SELECT prid, pname, ptype, Sum(qnty) As curstock
FROM (
SELECT s.prid, p_list.pname, p_list.ptype, s.qnty
FROM p_list INNER JOIN store As S ON p_list.prid = s.prid

UNION ALL

SELECT s.prid, p_list.pname, p_list.ptype, -s.qnty
FROM p_list INNER JOIN stockout As S p_list.prid = store.prid

) AS A
GROUP BY prid, pname, ptype

Note the negative sign before the s.qnty in the 2nd SELECT clause in the
UNION part of the query. This will subtract the stockout qnty from the
store qnty in the 1st SELECT's Sum().

The only problem is if the store qnty = zero and the stockout qnty > 0
then the curstock will be a negative number. If you're running this to
a report you can transpose the value in the report from a negative
number to a zero (in a Text Box's Control Source property):

=IIf(curstock < 0, 0, curstock)

Or, you might try this in the query (in the 1st SELECT clause):

SELECT .... IIf(Sum(qnty) < 0, 0, Sum(qnty)) As curstock

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ8cRbYechKqOuFEgEQKcSACg7iRgbJ5Expj2orLSDXPBbyNuKeQAnjHa
frTAsF8JwYlzL8FtH1NFr+JC
=VgTw
-----END PGP SIGNATURE-----