MS Access - Sum() not calculating correctly

[amaklop]

Hi

Hope someone can help. I have a simple sql :

SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
This query returns a total of 272111.46

When I run the following however,

select sum(tot) as GrandTot
from (
SELECT age, sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
group by age)
I get the following :

278111.46

The second result 278111.46 is the correct one.

Any ideas?

 

[arnelgp]

what about the result if you do the sum from the base table.

 

[amaklop]

Further to the above .....

The following sql returns 272111.46 :

select sum(tot)
from (
SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634)

This sql returns 278111.46:

select sum(tot)
from (
SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
and trndate > datevalue('2016/05/01'))
and this sql returns nothing :

select sum(tot)
from (
SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
and trndate <= datevalue('2016/05/01'))

 

[The_Doc_Man]

Is there a chance that some record for customerid 634 could have exactly a -1000.00 credit? If so, see if there is anything else that is special about that record. Obviously, if you have a random error on input, any difference would be possible, but a difference of exactly $1000 is awfully conveniently rounded.

As a test, get a count of the records for each of those queries - the standalone query and the inner query - to see if you are dealing with the same number of records. I.e. as well as getting SUM(Credit) as tot, also do Count(Credit) as Creccnt in the same query. The presence of the GROUP BY should not make a big difference.

EDIT: Our posts crossed. The fact that date selectivity is involved and that you have exactly 6000.00 difference tells me that you have an issue with some records that might have bad dates. (Neatly rounded differences are ALWAYS suspect.) In the first query, there is a reference to something called "AGE" and it is involved in ordering records (GROUP BY). What is "age" in this context? Is it a computation in the query that is the record source q_ageanalysislines for the two queries you presented?

 

[amaklop]

Breaking it down even further (sorry arnelgp, will answer you just after this) :

SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
Returns : 272111.46


SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
and trndate > datevalue('2016/05/01')
Returns: 278111.46


and
SELECT sum(credit) as tot
FROM q_ageanalysislines
where customerid = 634
and trndate <= datevalue('2016/05/01')
returns nothing

 

[amaklop]

SELECT count(1)
FROM q_ageanalysislines
where customerid = 634
Result : 83


SELECT count(1)
FROM q_ageanalysislines
where customerid = 634
and trndate > datevalue('2016/05/01')
Result : 84

SELECT count(1)
FROM q_ageanalysislines
where customerid = 634
and trndate <= datevalue('2016/05/01')
Result = 0


Mmm ... ok .... So now to figure out why the first query is dropping a record

 

[amaklop]

SELECT count(1)
FROM q_ageanalysislines
where customerid = 634
and credit = 6000
Result : 3

SELECT *
FROM q_ageanalysislines
where customerid = 634
and credit = 6000
Returns 4 records

 

[amaklop]

Sorry arnelgp, getting back to your post ... do you mean running the query against the table that sources the q_ageanalysislines? The q_ageanalysislines is a complex query with a couple of union statements and 5 union statements

 

[amaklop]

arnelgp, I did what I think you suggested. I took the base code of the query q_ageanalysislines, put a select sum(credit) around it and I got to the correct 278111.46.

So the issue comes when I sum from the query q_ageanalysislines

 

[amaklop]

LOL ... this is getting weirder ....

SELECT count(1)
FROM q_ageanalysislines
where customerid = 634
and credit = 6000
Returns : 3


SELECT count(1), min(trndate), max(trndate)
FROM q_ageanalysislines
where customerid = 634
and credit = 6000
Returns:
4 - 2017/05/01 - 2018/07/07

 

[The_Doc_Man]

This points to an error in the q_ageanalysislines query that returns inconsistent results based on whether you include or omit the dates. Your simple query isn't your problem. It is the underlying query that is hosing you to tears.

 

[amaklop]

Hi grandpa, sorry only saw this question now ...

In the first query, there is a reference to something called "AGE" and it is involved in ordering records (GROUP BY). What is "age" in this context? Is it a computation in the query that is the record source q_ageanalysislines for the two queries you presented?

Yes, age is actually calculated based on current_date-TrnDate to get the age of the transaction. so it will be current, 30, 60, 90, 120

 

[amaklop]

Ok, here goes ... this is my q_ageanalysislines query. I'm an Oracle sql developer so my access skills aren't so hot. This query might not work that well on access so any tips will help :

SELECT customerid, c.company, fix(100*v_totage120)/100 AS totage120, fix(100*v_totage90)/100 AS totage90, fix(100*v_totage60)/100 AS totage60, fix(100*v_totage30)/100 AS totage30, fix(100*v_totagecurr)/100 AS totagecurr, fix(100*v_totowing)/100 AS totowing
FROM (SELECT customerid, (Iif(amt120days = 0
OR Nz(totpaid, 0) >= amt120days, 0, Nz(amt120days, 0) -
Nz(totpaid, 0))) AS v_totage120,
(Iif(amt90days = 0
OR Nz(totpaid, 0) >= amt120days + amt90days, 0, Iif(
Nz(totpaid, 0) > amt120days
AND ( amt90days > ( Nz(
totpaid, 0)
- amt120days ) ),
amt90days - ( Nz(totpaid, 0) - amt120days ), amt90days))) AS v_totage90,
(Iif(amt60days = 0
OR Nz(totpaid, 0) >= ( amt120days + amt90days + amt60days ), 0
,
Iif(Nz(totpaid, 0) > ( amt120days + amt90days )
AND ( amt60days > ( Nz(totpaid, 0) - (
amt120days + amt90days )
) ),
amt60days - ( Nz(totpaid, 0) - amt120days - amt90days ),
amt60days))
) AS v_totage60,
(Iif(amt30days = 0
OR Nz(totpaid, 0) >= ( amt120days + amt90days + amt60days +
amt30days
), 0,
Iif(Nz(totpaid, 0) > ( amt120days + amt90days + amt60days )
AND ( amt30days > ( Nz(totpaid, 0) - ( amt120days + amt90days +
amt60days
)
) ), amt30days - ( Nz(totpaid, 0) - amt120days
- amt90days
- amt60days ), amt30days))) AS v_totage30,
(Iif(currbal = 0
OR Nz(totpaid, 0) >= ( amt120days + amt90days + amt60days +
amt30days
+ currbal ), 0,
Iif(Nz(totpaid, 0) > ( amt120days + amt90days + amt60days +
amt30days )
AND ( currbal > ( Nz(totpaid, 0)
- (
amt120days + amt90days +
amt60days +
amt30days
)
) ), currbal - ( Nz(totpaid, 0)
- amt120days
- amt90days
- amt60days
- amt30days ),
currbal))) AS v_totagecurr,
amt120days + amt90days + amt60days + amt30days + currbal - totpaid AS v_TotOwing
FROM (SELECT customerid,
totcredit AS totpaid,
Sum(Iif(agebucket = '120', tot, 0)) AS amt120days,
Sum(Iif(agebucket = '90', tot, 0)) AS amt90days,
Sum(Iif(agebucket = '60', tot, 0)) AS amt60days,
Sum(Iif(agebucket = '30', tot, 0)) AS amt30days,
Sum(Iif(agebucket = 'Current', tot, 0)) AS CURRbal
FROM (SELECT *
FROM (SELECT q.customerid,
q.custaccno,
q.company,
Sum(q.debit) AS tot,
q.age AS agebucket,
c.totcredit
FROM q_ageanalysislines AS q
INNER JOIN (SELECT customerid,
Sum(credit) AS totcredit
FROM q_ageanalysislines
GROUP BY customerid) AS c
ON c.customerid = q.customerid
GROUP BY q.customerid,
q.custaccno,
q.company,
q.age,
c.totcredit) AS a) AS b
GROUP BY customerid,
totcredit) AS lev1

) AS x LEFT JOIN customers AS c ON c.id = x.customerid
WHERE v_totowing <> 0
ORDER BY v_totowing;

 

[amaklop]

Although ... as I replied to arnelgp .. when I do a sum(credit) using the sql of the query, I get the correct figure:

select sum(credit)
from (
SELECT customers.company,
customers.id,
customers.custaccno,
customers.address1,
customers.address2,
customers.address3,
customers.address4,
a.customerid,
a.trntype,
a.trndate,
a.docid,
a.refno,
Iif(a.trndate > Date() - 30, 'Current',
Iif(a.trndate BETWEEN
Date() - 59 AND Date() - 30, '30', Iif(
a.trndate BETWEEN Date() - 89 AND Date()
- 60, '60', Iif(
a.trndate BETWEEN Date() - 119 AND Date(
) - 90, '90', Iif(
a.trndate <= Date()
- 120, '120'))))) AS age,
Round(a.debit, 2) AS debit,
Round(a.credit, 2) AS Credit,
trntype & ' ' & refno AS Description,
Date() AS joinkey,
Nz(a.paidind, '0') AS PaymentId
FROM (SELECT customerid,
'Opening Balance' AS trntype,
openbaldate AS trndate,
'' AS docid,
'' AS refno,
Iif(openbal < 0, openbal * -1, 0) AS debit,
Iif(openbal >= 0, openbal, 0) AS credit,
'0' AS paidind
FROM t_openbal
WHERE openbal <> 0
UNION
SELECT t_refunds.customerid,
t_refunds.referencedescr,
t_refunds.refunddate,
t_refunds.id,
'' AS refno,
0 AS debit,
t_refunds.amount AS credit,
'0' AS paidind
FROM t_refunds
LEFT JOIN t_openbal
ON t_refunds.customerid = t_openbal.customerid
WHERE t_refunds.refunddate >= openbaldate
OR Nz(openbaldate, 0) = 0
UNION
SELECT t_payments.customerid,
'Payment - Thank you' AS expr1,
t_payments.paymentdate,
t_payments.ourrefno AS docid,
t_payments.custrefno,
0 AS debit,
Round(t_payments.paymentamount, 2) AS credit,
Iif(Nz(invoice, 0) <> 0, t_payments.id, '0') AS paidind
FROM t_payments
LEFT JOIN t_openbal
ON t_payments.customerid = t_openbal.customerid
WHERE paymentdate >= openbaldate
OR Nz(openbaldate, 0) = 0
UNION
SELECT q_cnpercustomer.customerid,
'Credit Note',
creditnotedate,
'CN' &creditnoteid,
referenceno AS refno,
0 AS debit,
total AS credit,
'0' AS paidind
FROM q_cnpercustomer
LEFT JOIN t_openbal
ON q_cnpercustomer.customerid = t_openbal.customerid
WHERE ( creditnotedate >= openbaldate
OR Nz(openbaldate, 0) = 0 )
AND NOT EXISTS (SELECT invoiceno
FROM q_invoicepercustomer
WHERE Nz(invoiceno, 0) = Nz(originalinvoice, 0)
AND Nz(q_cnpercustomer.customerid, 0) = Nz
(
q_invoicepercustomer.customerid, 0))
UNION
SELECT customerid,
expr1,
custorderdate,
expr2,
referenceno,
debit,
credit,
pd
FROM (SELECT q_invoicepercustomer.customerid,
'Invoice' AS expr1,
q_invoicepercustomer.custorderdate,
'IN' &invoiceno AS expr2,
q_invoicepercustomer.referenceno,
q_invoicepercustomer.total AS debit,
0 AS credit,
Nz(paidind, '0') AS pd,
Nz(q_cnpercustomer.originalinvoice, 0) * 100 AS originv
FROM (q_invoicepercustomer
LEFT JOIN t_openbal
ON q_invoicepercustomer.customerid =
t_openbal.customerid)
LEFT JOIN q_cnpercustomer
ON Nz(q_invoicepercustomer.invoiceno, 0) = Nz(
q_cnpercustomer.originalinvoice, 0)
AND Nz(q_cnpercustomer.customerid, 0) = Nz(
q_invoicepercustomer.customerid, 0)
WHERE ( custorderdate >= openbaldate
OR Nz(openbaldate, 0) = 0 )
AND invoiceno <> '')
WHERE originv = 0) AS a
INNER JOIN customers
ON a.customerid = customers.id
WHERE Nz(a.paidind, '0') = 0
and customerid = 634
)

 

[amaklop]

Well ... I seem to have solved it ..

I took q_ageanalysislines, removed one of the union statements to see if I could eliminate the problem. The results were correct. I added the union statement back again and now the query returns the correct results.

Don't really understand why that fixed it but it's fixed ..

Thanks for your help

 

[The_Doc_Man]

Removing and adding back a component might just be all you need to eliminate a small spot of corruption. Hard to tell sometimes. But removing and restoring elements DOES force the SQL analyzer to re-evaluate things from scratch. I.e. to the SQL analyzer, that is nothing more or less than a new query each time you edit. So if you happened to "fix" something that you didn't realize was broken in a particular way, good for you!

It WOULD have been nice if in the process, you found something that was flawed so that you know what you fixed.

May I offer one more thought? For maintenance purposes you MIGHT wish to consider breaking up your humongous query into its component parts that can be separately tested. THEN write a UNION of the individual queries - where the WHERE clauses are in the individual queries so the encapsulating query doesn't need them.

Also, food for thought... there is one more difference between the two queries originally posted in your #1 post of this thread: The GROUP BY in the "inner query" is drastically different than the unordered query that was a stand-alone query. The records in UNION queries can be sorted within each union component but overall the records appear in the order of the contribution from the UNION components, i.e. in the order the UNIONs were presented. So if anything was order-sensitive then you could have run into an issue there. Offhand I didn't see anything but that is a rather daunting query.