Query containing date range does not show "0" if no records

[Bob Quintal]

I have developed a database that among other things is used to
track attendance information for agents working on a helpdesk
project. I have written queries that pull monthly totals, and
a rolling 12 month total. My problem is that if an agent does
not have an attendance record for any given month, they do not
show up on the report. (i.e. someone starting this month, July
2007 would not show up on the rolling 12 report unless they
have records for each of the past 12 months) I have a work
around in place, adding a 'place holder' record for each of
the previous month. This problem also occurs when we begin a
new month, agents do not show up on the report until they have
a record for that month which may be 2-3 days if the month
begins on days they are scheduled off. Any help that anyone
can provide would be greatly appreciated

Good design says you should have a table somewhere with each
employee data, 1 record per employee.

Add the field to your existing summary queries, left joined to
the existing data ones so that the employee Id will appear, even
if there are no records in the summary query.

 

[Pete]

I have developed a database that among other things is used to track
attendance information for agents working on a helpdesk project. I have
written queries that pull monthly totals, and a rolling 12 month total. My
problem is that if an agent does not have an attendance record for any given
month, they do not show up on the report. (i.e. someone starting this month,
July 2007 would not show up on the rolling 12 report unless they have records
for each of the past 12 months) I have a work around in place, adding a
'place holder' record for each of the previous month. This problem also
occurs when we begin a new month, agents do not show up on the report until
they have a record for that month which may be 2-3 days if the month begins
on days they are scheduled off. Any help that anyone can provide would be
greatly appreciated

 

[Jackie L]

If you have an Agents table (which you should), then in your query, do an
outer join from the Agents table to the Attendance table. Make sure to use
the AgentID and name from the agents table. If you would like a zero value
for the records with no attendance data, then use either the Nz function or
IIF/IsNull in and expression to convert the blank records to zero.

Hope this helps.

 

[Pete]

I already have a one-to-many join set up. The following is Access's
description of the join, which logically seems correct to me: (Include ALL
records from 'Agent Data (Basic, HR, Personal)' and only those records from
'Attendance' where the joined fields are equal.) In my query, I have the
Agent ID, as well as the name info coming in from the Agent Data table, and
the attendance summary coming in from the the attendance table. The Agent ID
field is the only common thread among the two tables (or any of the tables in
the database for that matter). There is no name information stored in the
Attendance table. Also, I have found that if I remove the date range from my
query it does show everyone, and the agents without attendance records at all
(do show up with blank records. As soon as I put the date criteria back into
the query they disappear.

 

[Pete]

I already have a one-to-many join set up. The following is Access's
description of the join, which logically seems correct to me: (Include ALL
records from 'Agent Data (Basic, HR, Personal)' and only those records from
'Attendance' where the joined fields are equal.) In my query, I have the
Agent ID, as well as the name info coming in from the Agent Data table, and
the attendance summary coming in from the the attendance table. The Agent ID
field is the only common thread among the two tables (or any of the tables in
the database for that matter). There is no name information stored in the
Attendance table. Also, I have found that if I remove the date range from my
query it does show everyone, and the agents without attendance records at all
(do show up with blank records. As soon as I put the date criteria back into
the query they disappear.

 

[Bob Quintal]

I already have a one-to-many join set up. The following is
Access's description of the join, which logically seems
correct to me: (Include ALL records from 'Agent Data (Basic,
HR, Personal)' and only those records from 'Attendance' where
the joined fields are equal.) In my query, I have the Agent
ID, as well as the name info coming in from the Agent Data
table, and the attendance summary coming in from the the
attendance table. The Agent ID field is the only common
thread among the two tables (or any of the tables in the
database for that matter). There is no name information
stored in the Attendance table. Also, I have found that if I
remove the date range from my query it does show everyone, and
the agents without attendance records at all (do show up with
blank records. As soon as I put the date criteria back into
the query they disappear.

That's because the ones with nulls fail to meet the criteria.

You need to make the left join after having done your
filter/summary query. You could do that in a subquery to the select
query where you join the employees table, but it's so much easier
to simply do the summary query and then do the join in a second.

Or you could try to add to the existing criteria: OR mydate is
null.

Good design says you should have a table somewhere with each
employee data, 1 record per employee.

Add the field to your existing summary queries, left joined
to the existing data ones so that the employee Id will
appear, even if there are no records in the summary query.