Including all records from employees in a date range

[Amy]

I have two tables, one is for employees and the other I use to track crime
report numbers. I need a query to total the number of arrests, warrants,
adults, juveniles, etc. for a specific month in order to do the monthly
stats. However, I need to include all the employees regardless of whether
they had any reports or not, as I have to also find the average reports per
squad. I need the query to include all deputies. As long as I run the query
without any date conditions, I get all the deputies; however, when I input a
specific month/year for the totals, only those deputies who have information
for that month return.

Fields included from the tblEmployees are:
intEmpID
chrSquad
chrTitle
chrLast Name

Fields included from the tblMain are:
dteDate (Date By Month: Format$([DB 2006].[Date],'mmmm yyyy'))
Sum of intArrest
Sum of intWarrant
Sum of intCapias
Sum of intFelony
Sum of intMisdemeanor
Sum of intJuvenile
Sum of Adult

What I want to do is place the following condition on the dtedate field of
the query: ['Enter Month and Year'] and have all the deputies show up even if
they have a null value for that month. Am I confusing anyone yet??

 

[John W. Vinson]

What I want to do is place the following condition on the dtedate field of
the query: ['Enter Month and Year'] and have all the deputies show up even if
they have a null value for that month. Am I confusing anyone yet??

Use a "Left Join" on the query - select the join line and choose the option
"Show all records in <table of deputies> and matching records in <the other
table>" - and use a criterion

[Enter Month and Year] OR IS NULL

on the dtedate field.

John W. Vinson [MVP]

 

[Amy]

Duh! I knew it was something easy I was overlooking. Sometimes we tend to
get so caught up in the advanced aspects, that we forget the most simple
solution. Thanks for turning on the light bulb...grin.

What I want to do is place the following condition on the dtedate field of
the query: ['Enter Month and Year'] and have all the deputies show up even if
they have a null value for that month. Am I confusing anyone yet??

Use a "Left Join" on the query - select the join line and choose the option
"Show all records in <table of deputies> and matching records in <the other
table>" - and use a criterion

[Enter Month and Year] OR IS NULL

on the dtedate field.

John W. Vinson [MVP]