If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dsum Problem with date criteria
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. |
#2
|
|||
|
|||
Dsum Problem with date criteria
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(qty0, qty, 0)) AS receivedQty, SUM(iif(qty0, -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" 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"), 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. |
Thread Tools | |
Display Modes | |
|
|