MS ACCESS: How to extract quantity consumption quarterly according to transactionDate by section

[Kamayo Ako]

tblInventory - Filtered by section ADMINISTRATIVE SECTION

Trans Date ItemID Description Unit Price IstQtr 2ndQtr 3rdQtr 4thQtr
Qty Amount Qty Amount Qty Amount Qty Amount
Feb. 14, 2023 1 AIR FRESHENER, 280mL/150g min can 715 5 3,575 10 7,150 10 7,150 10 7,150


Thank you so much for continuing support.

Happy Valentines and God Bless you All

 

[arnelgp]

you mean Quarter of the Current year?

you create 4 Total Queries that will sum the Consumption Per Quarter,

example, qry1Qtr:

select ItemID, Sum(Qty) As SumOfQty, Sum(Amount) As SumOfAmount
from transactionTable
where [Trans Date] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()), 3, 31)
group by ItemID;

qry2Qtr:

select ItemID, Sum(Qty) As SumOfQty, Sum(Amount) As SumOfAmount
from transactionTable
where [Trans Date] Between DateSerial(Year(Date()),4,1) And DateSerial(Year(Date()), 6, 30)
group by ItemID;

...etc.

create the Final query joining your inventory table to the 4 quarterly queries:

SELECT inventory.ItemID,
qry1Qtr.SumOfQty AS 1QtrConsump,
qry2Qtr.SumOfQty AS 2QtrConsump,
qry3Qtr.SumOfQty AS 3QtrConsump,
qry4Qtr.SumOfQty AS 4QtrConsump
FROM (((inventory LEFT JOIN qry1Qtr ON inventory.ItemID = qry1Qtr.ItemID)
LEFT JOIN qry2Qtr ON inventory.ItemID = qry2Qtr.ItemID)
LEFT JOIN qry3Qtr ON inventory.ItemID = qry3Qtr.ItemID)
LEFT JOIN qry4Qtr ON inventory.ItemID = qry4Qtr.ItemID;

 

[Kamayo Ako]

you mean Quarter of the Current year?

you create 4 Total Queries that will sum the Consumption Per Quarter,

example, qry1Qtr:

select ItemID, Sum(Qty) As SumOfQty, Sum(Amount) As SumOfAmount
from transactionTable
where [Trans Date] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()), 3, 31)
group by ItemID;

qry2Qtr:

select ItemID, Sum(Qty) As SumOfQty, Sum(Amount) As SumOfAmount
from transactionTable
where [Trans Date] Between DateSerial(Year(Date()),4,1) And DateSerial(Year(Date()), 6, 30)
group by ItemID;

...etc.

create the Final query joining your inventory table to the 4 quarterly queries:

SELECT inventory.ItemID,
qry1Qtr.SumOfQty AS 1QtrConsump,
qry2Qtr.SumOfQty AS 2QtrConsump,
qry3Qtr.SumOfQty AS 3QtrConsump,
qry4Qtr.SumOfQty AS 4QtrConsump
FROM (((inventory LEFT JOIN qry1Qtr ON inventory.ItemID = qry1Qtr.ItemID)
LEFT JOIN qry2Qtr ON inventory.ItemID = qry2Qtr.ItemID)
LEFT JOIN qry3Qtr ON inventory.ItemID = qry3Qtr.ItemID)
LEFT JOIN qry4Qtr ON inventory.ItemID = qry4Qtr.ItemID;

Yes sir. I will do this sir . Thank you so much