How to count NULL query results as 0?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If a query returns no result, how can that result be treated as a 'zero' rather than just producing an empty result. For example: I write a query that counts the number of software licences available and the numbers actually deployed, but if no licences are deployed, rather than reporting that the count is zero, the query simply returns nothing. The SQL is:

SELECT qry_AllSoftwareByVersion.Company, qry_AllSoftwareByVersion.Title, qry_AllSoftwareByVersion.Version, qry_AllSoftwareByVersion.N_of_Licence, qry_CountSoftwareEquivalences.CountOfWS_NUM, [N_of_Licence]-[CountOfWS_NUM] AS Licences_Remaining
FROM qry_AllSoftwareByVersion INNER JOIN qry_CountSoftwareEquivalences ON (qry_AllSoftwareByVersion.Company = qry_CountSoftwareEquivalences.Company) AND (qry_AllSoftwareByVersion.Title = qry_CountSoftwareEquivalences.Title) AND (qry_AllSoftwareByVersion.Version = qry_CountSoftwareEquivalences.Version);

The query is based on two other queries: one reports the numbers of licences, the other the number of deployments.
 
Look into the Nz function which converts null values to
zero. The Nz function would have to be used on your count
value. I assume that CountOfWS_NUM is being computed in a
subquery. Where ever CountOfWS_NUM is being computed, use
the Nz function to return zero.

HTH
Andy
-----Original Message-----
If a query returns no result, how can that result be
Click to expand...
treated as a 'zero' rather than just producing an empty
result. For example: I write a query that counts the
number of software licences available and the numbers
actually deployed, but if no licences are deployed, rather
than reporting that the count is zero, the query simply
returns nothing. The SQL is:
SELECT qry_AllSoftwareByVersion.Company,
Click to expand...
qry_AllSoftwareByVersion.Title,
qry_AllSoftwareByVersion.Version,
qry_AllSoftwareByVersion.N_of_Licence,
qry_CountSoftwareEquivalences.CountOfWS_NUM,
[N_of_Licence]-[CountOfWS_NUM] AS Licences_Remaining
FROM qry_AllSoftwareByVersion INNER JOIN
Click to expand...
qry_CountSoftwareEquivalences ON
(qry_AllSoftwareByVersion.Company =
qry_CountSoftwareEquivalences.Company) AND
(qry_AllSoftwareByVersion.Title =
qry_CountSoftwareEquivalences.Title) AND
(qry_AllSoftwareByVersion.Version =
qry_CountSoftwareEquivalences.Version);
The query is based on two other queries: one reports the
Click to expand...
numbers of licences, the other the number of deployments.
 
First change your INNER JOIN to a LEFT JOIN so you will return rows for all the
rows in the AllSoftWareByVersion query. Next, try using the Nz Function around
the various values that you need to return zero if they are null. If I have
correctly deduced which fields are in which table and what values you need to
set to zero, then the following query may work for you. Note that I have
aliased your queries to make it easier for me to read your query and to type in
a possible fix. You can build your query without the aliases (SWV and E).

SELECT SWV.Company, SWV.Title, SWV.Version, SWV.N_of_Licence,
Nz(E.CountOfWS_NUM,0), Nz([N_of_Licence],0)-Nz([CountOfWS_NUM],0) AS Licences_Remaining
FROM qry_AllSoftwareByVersion AS SWV LEFT JOIN
qry_CountSoftwareEquivalences AS E ON
(SWV.Company = E.Company) AND (SWVn.Title = E.Title) AND
(SWV.Version = E.Version);
 
Back
Top