TMY TMY - 1 month ago 10
SQL Question

Access 2016 - SQL Sum Query Doesn't Provide Sum, No errors

Goal / Problem



What I am trying to do is fairly simple. I have two fields,
InvoiceNbr
and
TransAmt
. In the table, there may be multiple transactions for each invoice. So I'm simply trying to sum all transactions for each invoice number so I'll have one transaction sum for each invoice number (which I can later subtract from a balance to get the remaining balance). Sounds simple, and typically is... but I'm having an issue (or bug? that DOES NOT result in an error)!

My Data



Here is a query that will give a snippet of my data:

SELECT [_MasterLog_Transactions].InvoiceNbr, [_MasterLog_Transactions].TransAmt
FROM _MasterLog_Transactions
GROUP BY [_MasterLog_Transactions].InvoiceNbr, [_MasterLog_Transactions].TransAmt
HAVING ((([_MasterLog_Transactions].InvoiceNbr)="16081702014741")
AND (([_MasterLog_Transactions].TransAmt)>0));


This results in:

InvoiceNbr TransAmt
16081702014741 $1,096.73
16081702014741 $3,668.25


If I add a 'totals' row using the Access GUI, and choose "SUM", it correctly calculates "$4,764.98". And for other random reference, in the
_MasterLog_Transactions
table,
TransAmt
is a 'number' field, field size 'double', and format 'currency' -- so it should be capable of being used with the SUM function.

My Query



So working from the above data, I created the query:

SELECT [_MasterLog_Transactions].InvoiceNbr,
Sum([_MasterLog_Transactions].TransAmt) AS MyTest
FROM _MasterLog_Transactions
GROUP BY [_MasterLog_Transactions].InvoiceNbr
HAVING ((([_MasterLog_Transactions].InvoiceNbr)="16081702014741"));


My Query Results



InvoiceNbr MyTest
16081702014741 $1,096.73


I would have expected:

InvoiceNbr MyTest
16081702014741 $4,764.98


No errors occur. It runs and Access seems to think it is outputting the proper result.

Am I overlooking something really simple here, or is this an Access bug I've never run into? Any help would be greatly appreciated! Thanks.

Answer

Please check for space in the InvoiceNbr.
You can verify that this is an InvoiceNbr issue by adding COUNT(*) to your query.

Comments