Showing Zero Values in a Query with a "Count" Totals Function

[Guest]

I'm trying to count records in a table titled [Discrepancies] based on one
field, [Squadrons] and another [Date Closed]. I have the [Date Closed]
criteria set to >#1/1/2005. I want to have all the squadrons displayed, even
if the number of records that meets the criteria is 0. Right now my query
will only return a count for squadrons that have at least one record meeting
the criteria. Thanks!

 

[Guest]

Squadrons? Hummm. Sounds USAF to me! Maybe Navy. As a retired USAF crew
chief, I'll offer the following.

You have an inner join between the two tables. What you need is a left join.
In the query double-click on the line linking the two tables until a dialog
box shows up. Chose the options that says something like All the Squadrons
but only Discrepancies where they match.

Now this won't show 0 for those squadrons without discrepancies; it will
show a blank on null value instead. I bet you'll be able to figure that one
out. Hint: Look at the NZ() function.

 

[Guest]

Thank you for your help, but now I'm really confused. I only have one table
in my query -> "Discrepancies." My query looks like this:

Field: Group Group Date Closed
Table: Discrepancies Discrepancies Discrepancies
Total: Group By Count Group By
Criteria: >#1/1/2005#

It returns a count of discrepancies for any group that has discrepancies
matching the "Date Closed" criteria, but nothing for a group that has no
matching "Date Closed" fields. Am I going about this all the wrong way?

Yes, I am in the AF. Stationed at Moody AFB, GA. Thanks again!

Squadrons? Hummm. Sounds USAF to me! Maybe Navy. As a retired USAF crew
chief, I'll offer the following.

You have an inner join between the two tables. What you need is a left join.
In the query double-click on the line linking the two tables until a dialog
box shows up. Chose the options that says something like All the Squadrons
but only Discrepancies where they match.

Now this won't show 0 for those squadrons without discrepancies; it will
show a blank on null value instead. I bet you'll be able to figure that one
out. Hint: Look at the NZ() function.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to count records in a table titled [Discrepancies] based on one
field, [Squadrons] and another [Date Closed]. I have the [Date Closed]
criteria set to >#1/1/2005. I want to have all the squadrons displayed, even
if the number of records that meets the criteria is 0. Right now my query
will only return a count for squadrons that have at least one record meeting
the criteria. Thanks!

 

[Guest]

Hi Joe,

Look me up in the Global Address List in Outlook. I'm there with a slightly
different first name!

Anyway if a Squadron can have more than one Discrepancy, you probably need
more than one table.

But for your immediate problem, try this:

#1/1/2005# Or Is Null

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thank you for your help, but now I'm really confused. I only have one table
in my query -> "Discrepancies." My query looks like this:

Field: Group Group Date Closed
Table: Discrepancies Discrepancies Discrepancies
Total: Group By Count Group By
Criteria: >#1/1/2005#

It returns a count of discrepancies for any group that has discrepancies
matching the "Date Closed" criteria, but nothing for a group that has no
matching "Date Closed" fields. Am I going about this all the wrong way?

Yes, I am in the AF. Stationed at Moody AFB, GA. Thanks again!

Squadrons? Hummm. Sounds USAF to me! Maybe Navy. As a retired USAF crew
chief, I'll offer the following.

You have an inner join between the two tables. What you need is a left join.
In the query double-click on the line linking the two tables until a dialog
box shows up. Chose the options that says something like All the Squadrons
but only Discrepancies where they match.

Now this won't show 0 for those squadrons without discrepancies; it will
show a blank on null value instead. I bet you'll be able to figure that one
out. Hint: Look at the NZ() function.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I'm trying to count records in a table titled [Discrepancies] based on one
field, [Squadrons] and another [Date Closed]. I have the [Date Closed]
criteria set to >#1/1/2005. I want to have all the squadrons displayed, even
if the number of records that meets the criteria is 0. Right now my query
will only return a count for squadrons that have at least one record meeting
the criteria. Thanks!

 

[John Vinson]

I'm trying to count records in a table titled [Discrepancies] based on one
field, [Squadrons] and another [Date Closed]. I have the [Date Closed]
criteria set to >#1/1/2005. I want to have all the squadrons displayed, even
if the number of records that meets the criteria is 0. Right now my query
will only return a count for squadrons that have at least one record meeting
the criteria. Thanks!

Use a criterion

#1/1/2005# OR IS NULL

to include even records with no value in the Date Closed field (and to
*exclude* records closed prior to that date).

John W. Vinson[MVP]