TMY TMY - 1 year ago 78
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,
. 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
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download