Sum of Query not reporting zero values

[Guest]

I have sum of query that will not report the zero values. For instance, If I
have a record with no cost in it, it should display Order and 0.00. But the
record won't show. As soon as I put a cost in it, it will display. Here is
the query:

SELECT Sum(tblServiceServiceTasks.ServiceTaskCost) AS SumOfServiceTaskCost,
tblServices.UnitID
FROM tblServices INNER JOIN tblServiceServiceTasks ON tblServices.ServiceID
= tblServiceServiceTasks.ServiceID
GROUP BY tblServices.UnitID;

Can anybody help?

 

[MGFoster]

I have sum of query that will not report the zero values. For instance, If I
have a record with no cost in it, it should display Order and 0.00. But the
record won't show. As soon as I put a cost in it, it will display. Here is
the query:

SELECT Sum(tblServiceServiceTasks.ServiceTaskCost) AS SumOfServiceTaskCost,
tblServices.UnitID
FROM tblServices INNER JOIN tblServiceServiceTasks ON tblServices.ServiceID
= tblServiceServiceTasks.ServiceID
GROUP BY tblServices.UnitID;

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a LEFT JOIN and the Nz() function:

SELECT S.UnitID, Nz(Sum(T.ServiceTaskCost),0) AS SumOfServiceTaskCost
FROM tblServices As S LEFT JOIN tblServiceServiceTasks As T
ON S.ServiceID = T.ServiceID
GROUP BY S.UnitID;

LEFT JOIN means return all rows in the left table, even if there isn't
anything related in the right table. If there isn't anything in the
ServiceTaskCost Sum() will return NULL. The Nz() function will change
that NULL to zero.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlWySYechKqOuFEgEQL1YgCgsFwWLLoaAKdlsuPLFaeA9vA6RjwAoLHn
Ieo4Y4aA2MmZuHSvULfBuucG
=caKj
-----END PGP SIGNATURE-----

 

[Guest]

I do have records in that table, in tblServiceServiceTasks I will have a
record with zero as the cost? It doesn't make sense why this doesn't work,
especially when it will work with this query, the only difference is the
extra table?

SELECT Sum(tblBodyDamages.BodyDamageCost) AS SumOfBodyDamageCost,
tblBodyDamages.UnitID
FROM tblBodyDamages
GROUP BY tblBodyDamages.UnitID;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Another difference between that query & the query you first posted is
the table name. ;-)

Note that I said "related" records. I.e., if there isn't a record in
table tblServiceServiceTasks w/ the same ServiceID as in table
tblServices an INNER JOIN will not show the row in tblServices.

E.g.:

INNER JOIN results

UnitID Sum
====== ====
1 3
2 4
5 6

LEFT JOIN results

UnitID Sum
====== ====
1 3
2 4
3 0 - ServiceTaskCost was NULL
4 0 - ServiceTaskCost was NULL
5 6

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQlW4ZIechKqOuFEgEQKujACg3UDF6QiAACdL7mcVcRMKKe+/mHQAn3xJ
DeTz8jXifQ0JhLjemgBAT0n3
=aHUK
-----END PGP SIGNATURE-----