Hi all,
Kindly solve my problem.

I have 2 tables  stock_received , stock_utilized.

stock_received fields -- date,3pcs,4pcs,boxes.

stock_utilized fields --   date,3pcs,4pcs,boxes.

I have successfully made a query to get the the current stock in hand as 

 select sum(3pcs)- dsum("3pcs","stock_utilized"), 
sum(4pcs)-dsum("4pcs","stock_utilized"),sum(boxes)-dsum("boxes","stock_utilized") from stock_received ;

Here i have a complex problem, I cant make query with date criteria.

for example 01/05/2010 to 30/05/2010 

I want to get how much stock received in that dates and how much was 
utilized and how much is balance.

kindly help me how should i write query.

It would be easier and faster (at execution time) to use a single table 
(with negative quantity for utilized items).

SELECT [3pcs] AS qty, [date] AS dateStamp FROM stock_received
UNION ALL
SELECT -[3pcs], [date]  FROM stock_utilized


as a saved query will do that 'table' (without indexes, though), assume it 
is called q1, then:

SELECT SUM(iif(qty>0, qty, 0)) AS receivedQty,
    SUM(iif(qty<0, -qty, 0)) AS utilizedQty,
    SUM(qty) AS brutQty
FROM q1
WHERE dateStamp BETWEEN #01/05/2010# AND #30/05/2010#

should to for the given date interval.


Vanderghast, Access MVP



"naveen prasad" <naveenprasad@discussions.microsoft.com> wrote in message 
news:C08F0320-30A9-43FC-80C3-B39FAF8CAB0C@microsoft.com...
> Hi all,
> Kindly solve my problem.
>
> I have 2 tables  stock_received , stock_utilized.
>
> stock_received fields -- date,3pcs,4pcs,boxes.
>
> stock_utilized fields --   date,3pcs,4pcs,boxes.
>
> I have successfully made a query to get the the current stock in hand as
>
> select sum(3pcs)- dsum("3pcs","stock_utilized"),
> sum(4pcs)-dsum("4pcs","stock_utilized"),sum(boxes)-dsum("boxes","stock_utilized") 
> from stock_received ;
>
> Here i have a complex problem, I cant make query with date criteria.
>
> for example 01/05/2010 to 30/05/2010
>
> I want to get how much stock received in that dates and how much was
> utilized and how much is balance.
>
> kindly help me how should i write query.
>
>