Im trying to design a query for an inventory management program .The qury is supposed to give me an opening balance as at a defined period, and transactions in and out up to a defined period.
I was hoping to accomplish this by using a union query which will be a union of these two querries A and B
A. First Query To Deal with Openning Balance
SELECT historylines.StockID, Sum(DSum("stkQty","Historylines","[Stockid]=" & (DLookUp("SelectedStock","HistoryPeriod")))) AS OpBal, DateValue([HistHeader].[tdate]) AS Expr1
FROM HistHeader RIGHT JOIN (historylines RIGHT JOIN Stocks ON historylines.stockid = Stocks.id) ON HistHeader.id = historylines.headerid
GROUP BY historylines.StockID, DateValue([HistHeader].[tdate])
HAVING (((historylines.StockID)=(DLookUp("SelectedStock","HistoryPeriod"))) AND ((DateValue([HistHeader].[tdate]))<DateValue(DLookUp("Start_date","HistoryPeriod"))));
This query is running , but :
1. The criteria limits it to values before 1 january 2019 but it is getting values before and after 1 jan 2019
2. It is not aggregating the quantities.
B. Second query to deal with itemised transactions after opening balance
SELECT historylines.StockID, historylines.STkQty, DateValue([HistHeader].[tdate]) AS Expr1, HistHeader.DocNumber, HistHeader.CusName
FROM HistHeader RIGHT JOIN (historylines RIGHT JOIN Stocks ON historylines.stockid = Stocks.id) ON HistHeader.id = historylines.headerid
GROUP BY historylines.StockID, historylines.STkQty, DateValue([HistHeader].[tdate]), HistHeader.DocNumber, HistHeader.CusName
HAVING (((historylines.StockID)=(DLookUp("SelectedStock","HistoryPeriod"))) AND ((DateValue([HistHeader].[tdate]))>=DateValue(DLookUp("Start_date","HistoryPeriod")) And (DateValue([HistHeader].[tdate]))<=DateValue(DLookUp("end_date","HistoryPeriod"))));
This query seems to be working fine so far.
My plan was to combine the two into a union query.
Do you think you can spot why the first query is misbehaving?
I was hoping to accomplish this by using a union query which will be a union of these two querries A and B
A. First Query To Deal with Openning Balance
SELECT historylines.StockID, Sum(DSum("stkQty","Historylines","[Stockid]=" & (DLookUp("SelectedStock","HistoryPeriod")))) AS OpBal, DateValue([HistHeader].[tdate]) AS Expr1
FROM HistHeader RIGHT JOIN (historylines RIGHT JOIN Stocks ON historylines.stockid = Stocks.id) ON HistHeader.id = historylines.headerid
GROUP BY historylines.StockID, DateValue([HistHeader].[tdate])
HAVING (((historylines.StockID)=(DLookUp("SelectedStock","HistoryPeriod"))) AND ((DateValue([HistHeader].[tdate]))<DateValue(DLookUp("Start_date","HistoryPeriod"))));
This query is running , but :
1. The criteria limits it to values before 1 january 2019 but it is getting values before and after 1 jan 2019
2. It is not aggregating the quantities.
B. Second query to deal with itemised transactions after opening balance
SELECT historylines.StockID, historylines.STkQty, DateValue([HistHeader].[tdate]) AS Expr1, HistHeader.DocNumber, HistHeader.CusName
FROM HistHeader RIGHT JOIN (historylines RIGHT JOIN Stocks ON historylines.stockid = Stocks.id) ON HistHeader.id = historylines.headerid
GROUP BY historylines.StockID, historylines.STkQty, DateValue([HistHeader].[tdate]), HistHeader.DocNumber, HistHeader.CusName
HAVING (((historylines.StockID)=(DLookUp("SelectedStock","HistoryPeriod"))) AND ((DateValue([HistHeader].[tdate]))>=DateValue(DLookUp("Start_date","HistoryPeriod")) And (DateValue([HistHeader].[tdate]))<=DateValue(DLookUp("end_date","HistoryPeriod"))));
This query seems to be working fine so far.
My plan was to combine the two into a union query.
Do you think you can spot why the first query is misbehaving?