Why do these two queries give me different answers?

T

Tony Williams

I have written two queries which total the same fields but I get two
different answers.

Here is Query1:
SELECT tblmaintabs.txtMonthlabel,
Sum([txtClientsdomfacsole]+[txtClientsdomidsole]+[txtClientsexpsole]+[txtClientsimpsole]+[txtstockfinsole]+[txtClientsABLsole])
AS [Sum Of txtTotNbrClientssole]
FROM tblmaintabs
GROUP BY tblmaintabs.txtMonthlabel
HAVING (((tblmaintabs.txtMonthlabel)=#9/1/2009#));

Here is Query2
SELECT tblmaintabs.txtMonthlabel, Sum(tblmaintabs.txtClientsdomfacsole) AS
SumOftxtClientsdomfacsole, Sum(tblmaintabs.txtClientsdomidsole) AS
SumOftxtClientsdomidsole, Sum(tblmaintabs.txtClientsexpsole) AS
SumOftxtClientsexpsole, Sum(tblmaintabs.txtClientsimpsole) AS
SumOftxtClientsimpsole, Sum(tblmaintabs.txtStockfinsole) AS
SumOftxtStockfinsole, Sum(tblmaintabs.txtClientsABLsole) AS
SumOftxtClientsABLsole
FROM tblmaintabs
GROUP BY tblmaintabs.txtMonthlabel
HAVING (((tblmaintabs.txtMonthlabel)=#9/1/2009#));

If I add the totals of Query 2 together they should give me the same answer
as Query 1 but they don't. I'm taking the same data from the same fields but
coming up with different totals.
Can anyone explain why?
Thanks
Tony
 
C

Crystal (strive4peace)

Hi Tony,

the first equation,
Sum([txtClientsdomfacsole] +[txtClientsdomidsole]
+[txtClientsexpsole]+...

is using the mathematical + operator
In Access, if any of the factors is Null, the whole sum is
Null as well. The way to prevent this is to wrap each
reference in nz (null to zero)

Sum(nz([txtClientsdomfacsole],0)
+nz([txtClientsdomidsole],0) + nz([txtClientsexpsole],0) +...

the second query does not use the + operator so no values
are missed

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access
(also includes links for Whistles and Bells, VBA chapters,
sample databases, etc)

*
:) have an awesome day :)
*
 
T

Tony Williams

Thanks to you both. I would never have got to that solution, copied and saved
for my archive!!! (which is getting bigger and bigger!)
Thanks again
Tony
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

HOW TO SUM TWO DIFFERENT QUERIES.. PLEASE HELP 1
Several parameter queries with same question 0

Top