Include zeros in a query

[EAB1977]

Hi guys,

In a query, how can I include zeros in a query? For example, I am
returning records for a the number of complaints I have for a given
department. I want to be able to include zeros in my query.


Plant Code Source Department Complaints
1 QC 4
2 CQA 5
3 FSR 0

Here's my query:

SELECT ProjectedTable.Code, ProjectedTable.SourceNum,
Sum(ProjectedTable.NumInspected) AS SumOfNumInspected,
Sum(ProjectedTable.NumWetInk) AS SumOfNumWetInk
FROM ProjectedTable
WHERE (((ProjectedTable.EnteredDate) Between #1/1/2006# And
#12/31/2006#))
GROUP BY ProjectedTable.Code, ProjectedTable.SourceNum

Any ideas?

 

[Jeff Boyce]

If you are saying that you have a row for Plant3, DeptFSR, but have no value
entered (i.e., Null) for Complaints, then in you query, use:
ComplaintCount: Nz([Complaints],0)
to generate a 0 from a Null.

If you have NO row with information if you have no complaints, you'll need
to take a slightly different approach.

First you need to know all possible Plants & Departments. Get these by
doing a query against their table(s).

Next you need to create a new query that uses the previous query and the
table of Complaints, joined, not with a "equi-join" but with a directional
(LEFT or RIGHT) join from the list of all Plants/Departments to the table of
complaints.

This will show ALL of the Plants/Departments and ANY of their complaints.
Use the Nz() function there to generate a 0 instead of a null.

Now run a query that counts/groups on that (second) query.

Regards

Jeff Boyce
Microsoft Office/Access MVP