Criteria in a DSUM Expression in Query not working (1 Viewer)

CresolZim

Registered User.
Local time
Tomorrow, 00:46
Joined
Mar 22, 2019
Messages
11
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?
 

Minty

AWF VIP
Local time
Today, 22:46
Joined
Jul 26, 2013
Messages
10,355
Welcome to AWF.

A few things to comment on; Generally you should avoid domain functions (DLookups etc) in queries, they are effectively a query in their own right and will be run for every record returned. This will make queries on larger datasets very slow. Normally if it's related data this can be far better achieved with a join.

Take a step back, and show us some sample data and what your desired results set is.
 

CresolZim

Registered User.
Local time
Tomorrow, 00:46
Joined
Mar 22, 2019
Messages
11
Thank you. Let me try it. My objective is to have a single row with the total of stock quantities as openningBalance from the Historylines table This is why Im using the dsum. The problem persists even after I have removed the Dlookup from the date value criteria even after insering the date delimiters
 

Minty

AWF VIP
Local time
Today, 22:46
Joined
Jul 26, 2013
Messages
10,355
Without seeing your data, its very hard to envisage what you want, however divide and conquer is the best way with this type of problem.

Start with a basic grouped query to get you near where you need, even if it's still got multiple records.

Then create another grouped total query based on the first one etc etc.
 

CresolZim

Registered User.
Local time
Tomorrow, 00:46
Joined
Mar 22, 2019
Messages
11
Thank you for your concern.Ive extracted some data to illustrate my problem.
Hislines

DocType CusName DocNumber tdate StockID STkQty UnitCost
DEL jm DEL00024 20/3/2019 Brown Sugar 5000 US$4.00
DEL mt DEL00026 13/2/2019 Brown Sugar 4788 US$2.00
DEL mt DEL00027 4/12/2018 Brown Sugar 1200 US$2.00
DEL mt DEL00028 15/11/2018 Brown Sugar 3222 US$2.00
DEL mt DEL00029 1/10/2018 Petrl 1000 US$1.40
DEL TC DEL00030 21/3/2019 Petrl 400 US$3.50
DEL TC DEL00031 15/3/2019 Petrl 661 US$4.00

Results of query before Dsum and period criteria

qryopBal
StockID OpBal TrDate
Petrl 2061 1/10/2018


Results after Applying Dsum and Criteria:
It is including figures after the cut off date which should be 1 january 2019.
The correct Opbal should be 1000 and not 2061
 

Users who are viewing this thread

Top Bottom