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
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
WHERE dateStamp BETWEEN #01/05/2010# AND #30/05/2010#
should to for the given date interval.
Vanderghast, Access MVP
"naveen prasad" <firstname.lastname@example.org> wrote in message
> 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"),
> 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.