query result to zero when no data found

[KLM]

I am new to Access. I have a table for daily issuance of a material from
warehouse. I am trying to create daily report but when the material is not
issued on a particular day the query return with no data, I would like the
query to return 0. Please suggest how to write the query so it can show zero
in the result when there is no entry for a particular date. I tried Nz() but
with no luck.

 

[John Spencer]

Do you have at table of materials?

What does you Daily Issuance query look like?

What is the structure of your tables? Table names and field names can help
someone guide you to a solution.

The solution will probably involve using your current query as a subquery in
a second query.

Open a new query
-- Add the materials table
-- Add your current query
-- Join the Materials table to your current query on the materials id
field(s)
(drag from materials table material id field to the current query
material id field)
-- double click on the join line and select ALL from the matierials table
and ONLY from the query
-- add the fields you want to see (make sure you add the materials fields
from the materials table)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[KLM]

Hi John, Thanks for your reponse, below is my real time situation.

I have a table called Site Safety incident statistics, It has colums of "ID,
Date, Name, FirstAidCase, LostTimeInjiury, MedicalRecordableCases". I am
trying to create a monthly report that will have cumulative sum of incidents
for "Year to Date" and Sum of incidents for a patricular month. (Example
YearToDate: FirstAidCases=10, LostTimeInjiury=5 and MonthToDate:
FirstAidCases=0, LostTimeInjiury=0)

I can create a query to show the total sum of incidents for the year. But
for Monthly query for the Month of March, there is no entry in the table as
there is no safety incidents. How to create a query that will show zero for
March and cumulative sum of incidents for Jan to March.

Sorry I may not have explained properly as I am completely new to access and
sql queries.