SQL - Printing Dates With 0 "Count" Records

S

scadav

Hi,

I am trying to run a query in access and the users of the system would
really like to see a report with dates even though there are 0 values
for the dates. For example, we are calculating how many times an
incident occurred between certain dates. They would like the output to
look like this:

4/1/06 - 5
4/2/06 - 3
4/3/06 - 0
4/4/06 - 0
4/5/06 - 2
4/6/06 - 0
4/7/06 - 1

I have built the following query which works when there are results,
but it will not print out the "0" value days:

SELECT EmployeesActivity.ActivityDate, Count(*) AS CountNum,
EmployeesActivity.Activity
FROM EmployeesActivity
GROUP BY EmployeesActivity.ActivityDate, EmployeesActivity.Activity
HAVING (((EmployeesActivity.ActivityDate) Between #3/28/2006# And
#4/5/2006#))
ORDER BY EmployeesActivity.ActivityDate;


Anyone help me get dates in the range even though they have 0's? Any
help would be appreciated.

Thanks.
 
D

Douglas J. Steele

The only possible way would be to have a table that contains one row for
each date in question, and then join the two tables together (using a Left
Join so that all the rows in the date table get shown, whether or not
there's a matching row in the Activity table)

SELECT DateTable.ActivityDate, Count(*) AS CountNum,
EmployeesActivity.Activity
FROM DateTable LEFT JOIN EmployeesActivity
ON DateTable.ActivityDate = EmployeesActivity.ActivityDate
GROUP BY DateTable.ActivityDate, EmployeesActivity.Activity
HAVING (((DateTable.ActivityDate) Between #3/28/2006# And
#4/5/2006#))
ORDER BY DateTable.ActivityDate;
 
S

scadav

I have another question related to a different report I am trying to
run. I would like to have a running count of the number of times an
incident occured for an particular employee, but the users do not want
the report grouped by that employee, they want it sorted based on date.
So the report should look something like this:


Date Name Incident Past
Incidents
1/4/2006 Joe Never Showed up 0
1/5/2006 Steve Never Showed Up 0
1/16/2006 Joe Left work early 1
1/16/2006 Sue Inappropriate Cond. 0
1/25/2006 Joe Left work early 2


I am having trouble getting the past incident running sum working based
on the employees database id. Any help here?

Thank you.
 
J

John Spencer

You could use the DCOUNT function or a ranking query to get the value. You
could use Dcount in a control on the form. The Control's source would look
something like the following. Substitute your table name for Incidents and
your field name for IncidentDate, etc.
=DCount("*","Incidents","IncidentDate<#" & [DateControl] & "#")
 
S

scadav

Hi John,

I looked at what you sent and I don't think it resolves my problem.
The way my results chart got formatted, may have caused confusion. I
hope the formating below helps. In the example below, I really need a
running count at the employee level. So for the given date range of
the report, the first incident on 1/4/06 for Joe must have been his
first because he has had no past incidents. Then on 1/16/06, Joe had
another incident which causes the Past Incidents field to no indicate
that he has had 1 incident in the past for the date range of the
report.


Date Name Incident Past Incidents
1/4/2006 Joe Never Showed up 0
1/5/2006 Steve Never Showed Up 0
1/16/2006 Joe Left work early 1
1/16/2006 Sue nappropriate Cond. 0
1/25/2006 Joe Left work early 2

I appreciate your repsonse, but I don't think your suggestion would
solve this problem. Please let me know if I am incorrect.

Thanks.
 
J

John Spencer

I think it will if you properly implement it and if I get the DCOUNT
statement correct.
-- Add a new control to the report
-- Set its control source as indicated (with the modification as follows) -
forgot to limit it to just the specific name.
=DCount("*","Incidents","IncidentDate<#" & [DateControl] & "# AND [Name]
= """ & [NameControl] & """")
-- Does that work? If not, what problems do you get.

By the way, do you have something more precise for identifying individuals
than name (first name only). For instance in my office we have three gals
namde Stephanie and three guys named John (out of 35 people).
 
S

scadav

Here is what implemented in the control:

=DCount("*","EmployeesActivity","[ActivityDate] > #3/1/2006#" & " AND
[DatabaseID] = " & [txt_DatabaseID])


The result I get is that the first person on the report has a value of
1. Then the numbers go as follows:


3
5
7
8
9
11
12
13
14
16
18


and there is only 1 person on the report that has two incidents for the
time period in question.

As for the identification of individuals, yes. I am actually using a
DatabaseID field from the table and the control on the form is called
txt_DatabaseID

Thanks for your time.
 
J

John Spencer

I believe what you want to count is the number of activities that occured
prior to the current activity for this individual on this date of this
record. It appears you also have a cut off (earliest date). So I would
expect.

=DCount("*","EmployeesActivity","[ActivityDate] >= #3/1/2006# AND
[ActivityDate] < #" & txtActivityDate & "# AND
[DatabaseID] = " & [txt_DatabaseID])

If DataBaseID is not numeric, then you would have to modify this statement
to add the string delimiters in.

Another method would involve calculating the ranking value in your query.
Perhaps you can post the query that the report is using.
 
S

scadav

John,

Thanks for all of your help. You solved this problem for me. I really
appreciate it: Here is the final resolution:

=DCount("*","EmployeesActivity","[ActivityDate] >= #3/1/2006# AND
[ActivityDate] < #" & [txt_ActivityDate] & "# AND [DatabaseID] = " &
[txt_DatabaseID] & " AND [Activity] = 14 AND DeletedActivity=False")

I also had the RunningSum property set incorrectly. All is working
perfectly. Thanks again.
 
Top