Why do these two queries give me different answers?

[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

 

[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
*

 

[KenSheridan via AccessMonster.com]

Tony:

Possibly due to Nulls. In arithmetical expressions Null propagates, e.g.
Null + 42 = Null. So in your first query if any of the columns is Null in a
row the whole expression will evaluate to Null for that row. With numerical
data it is often appropriate to give the column a default value of zero in
table design view, but if Nulls are allowed you can use the Nz function to
return a zero in place of a Null, so the expression in your first query would
become:

SUM(Nz([txtClientsdomfacsole],0)+Nz([txtClientsdomidsole],0)+Nz(
[txtClientsexpsole],0)+Nz([txtClientsimpsole],0)+Nz([txtstockfinsole],0)+Nz(
[txtClientsABLsole],0))

With summation any Nulls in the set being summed will simply be ignored, so
the second query wouldn't need any amendment.

BTW you've used a HAVING clause here, when you should really use a WHERE
clause. This results from putting the criterion under the column by which
the query is grouped. The correct way is to add the txtMonthlabel column
again as another column to the design grid, select 'where' in the 'total' row,
uncheck 'show' and out the criterion in that column. The results will be the
same in this case, but a HAVING clause is really for use where you want it to
operate after the grouping e.g. to return all customers who’ve ordered more
that 10000 worth of goods with:

HAVING SUM(Amount) > 10000

Ken Sheridan
Stafford, England

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

 

[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

 

[Crystal (strive4peace)]

you're welcome, Tony happy to help

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
*