Sub Query, Inconsistent Results (1 Viewer)

[GK in the UK]

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.

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.

 

[cheekybuddha]

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]

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.