Sub Query, Inconsistent Results (1 Viewer)

GK in the UK

Registered User.
Local time
Today, 12:56
Joined
Dec 20, 2017
Messages
217
What is going on with this query to give me these results?

The problem column is AcBalance which is giving me garbage or sometimes the right result. Of 130 records I get c.40 with the right result.
The sub query qry_gross_value_total when run in isolation give me a correct result with all rows either 0 or a value, no nulls.
The column UnmatchedTrans gives me the correct result on every row, which is the number of child records of parent tblTransHeaders where qValueAllocated <> qGrossValue.

I don't understand how, if qGrossValue is seemingly doing it's job correctly on every row, it's not showing the correct total in AcBalance.

Code:
SELECT tblcustssupps.custsuppid,
       tblcustssupps.custsuppref,
       tblcustssupps.cscompanyname,
       tblcustssupps.cstown,
       tblcustssupps.cspostcode,
       Sum(qry_gross_value_total.qgrossvalue) AS AcBalance,
       Sum(Iif(( Nz([qgrossvalue], 0) - Nz([qvalueallocated], 0) ) <> 0, 1, 0)) AS UnmatchedTrans
FROM   tblcustssupps
       INNER JOIN ((tbltransheaders
                   LEFT JOIN qry_gross_value_total
                          ON tbltransheaders.transheaderid =
                             qry_gross_value_total.tltransheaderfk)
                   LEFT JOIN qry_allocated_value_total
                          ON tbltransheaders.transheaderid =
                             qry_allocated_value_total.alloctransheaderfk)
               ON tblcustssupps.custsuppid = tbltransheaders.thcustsuppfk
WHERE  (( ( tblcustssupps.cstype ) = 'S' ))
GROUP  BY tblcustssupps.custsuppid,
          tblcustssupps.custsuppref,
          tblcustssupps.cscompanyname,
          tblcustssupps.cstown,
          tblcustssupps.cspostcode
HAVING (( ( Sum(Iif(( Nz([qgrossvalue], 0) - Nz([qvalueallocated], 0) ) <> 0, 1,0)) )>0 ))
ORDER  BY tblcustssupps.custsuppref;

Row, 4, showing UnmatchedTrans = 5, has just 5 child records where AcBalance should be 0.
If I go into my application and match 2 transactions against each other, the 5 goes to 3(correctly) but AcBalance doesn't change.
It's not meant to but it should be showing 0.



Query1.jpg

Query2.jpg
Query1.jpg
Query2.jpg
 

cheekybuddha

AWF VIP
Local time
Today, 12:56
Joined
Jul 21, 2014
Messages
557
Hi,

At a guess, it seems as if your AcBalance expression is not taking account of transactions that have been matched.

Whether this should happen in the expression in this query, or in qry_gross_value_total it is difficult for me to tell
 

GK in the UK

Registered User.
Local time
Today, 12:56
Joined
Dec 20, 2017
Messages
217
The problem was the syntax of the sub query but I don't know why.

Here's the sub query that sometimes returns garbage:

SELECT tblTransLines.tlTransHeaderFK, Sum(Nz([tlNetValue],0)+Nz([tlVatValue],0)) AS qGrossValue
FROM tblTransLines
WHERE (((tblTransLines.tlLineType)=0))
GROUP BY tblTransLines.tlTransHeaderFK;

If I remove the NZ function, it works:

SELECT tblTransLines.tlTransHeaderFK, Sum([tlNetValue]+[tlVatValue]) AS qGrossValue
FROM tblTransLines
WHERE (((tblTransLines.tlLineType)=0))
GROUP BY tblTransLines.tlTransHeaderFK;

I had no idea that NZ in a query would returns such weird results.
 

Users who are viewing this thread

Top Bottom