Count queries -- make zeros show

  • Thread starter Thread starter kahaymon
  • Start date Start date
K

kahaymon

I need a count query to tell me when there are zero records, rather than
nothing show up. How do I accomplish this?
 
kahaymon said:
I need a count query to tell me when there are zero records, rather
than nothing show up. How do I accomplish this?
Click to expand...

What have you tried?

Select count(*) as Recordcount from table

shoud do what you want.
 
Sorry-I remembered Sequal View (no proficiency in sequal)
This is the code that is there:

SELECT [Inpt Units].Unit, [Incident Investigation].[Month/year],
Count([Incident Investigation].ID) AS CountOfID
FROM [Incident Investigation] RIGHT JOIN [Inpt Units] ON [Incident
Investigation].[Unit location of incident] = [Inpt Units].Unit
WHERE ((([Incident Investigation].Type)="Fall") AND (([Incident
Investigation].Involving)="I") AND (([Incident Investigation].[Date of
incident]) Between #1/1/2007# And [enter end date]))
GROUP BY [Inpt Units].Unit, [Incident Investigation].[Month/year];

In this query I have a table of Incidents and a table of units that I want
to limit my query to. I am querying unit and month/yr (which is one field)
and want a count of incidents (falls). When a unit does not have a fall in a
month/yr, there is no result. I would like to have unit, month/yr, and
number of falls without regard to zero or number....

Thanks.
 
To use the sql statement I posted:
1. Create a new query in Design View,
2. Close the Choose Table dialog without selecting a table
3. Switch to SQL View using the View menu, or the toobar button, or the
right-click menu
4. Paste the sql statement into the window
5. Fix the name of the table and run the query

To create something similar in the query builder grid:
1. Create a new query in Design View
2. Select your table from the list and close the dialog
3. Click the "Totals" button in the toolbar so a Total: row gets added
to the grid
4. Enter RecordCount: Count(*) into the top row of the first column
5. Select "Expression" from the dropdown list in the Total: row of the
same column.
6. Run the query.
 
kahaymon said:
Sorry-I remembered Sequal View (no proficiency in sequal)
This is the code that is there:

SELECT [Inpt Units].Unit, [Incident Investigation].[Month/year],
Count([Incident Investigation].ID) AS CountOfID
FROM [Incident Investigation] RIGHT JOIN [Inpt Units] ON [Incident
Investigation].[Unit location of incident] = [Inpt Units].Unit
WHERE ((([Incident Investigation].Type)="Fall") AND (([Incident
Investigation].Involving)="I") AND (([Incident Investigation].[Date of
incident]) Between #1/1/2007# And [enter end date]))
GROUP BY [Inpt Units].Unit, [Incident Investigation].[Month/year];

In this query I have a table of Incidents and a table of units that I
want to limit my query to. I am querying unit and month/yr (which is
one field) and want a count of incidents (falls). When a unit does
not have a fall in a month/yr, there is no result. I would like to
have unit, month/yr, and number of falls without regard to zero or
number....
Click to expand...
If i understand correctly, you will want to change this to:

SELECT [Inpt Units].Unit, [Incident Investigation].[Month/year],
Sum(IIF([Incident Investigation].[Type]="Fall", 1, 0))
AS NumberOf Falls
FROM [Incident Investigation] RIGHT JOIN [Inpt Units] ON [Incident
Investigation].[Unit location of incident] = [Inpt Units].Unit
WHERE (([Incident Investigation].Involving)="I")
AND (([Incident Investigation].[Date of incident])
Between #1/1/2007# And [enter end date]))
GROUP BY [Inpt Units].Unit, [Incident Investigation].[Month/year];
 
Back
Top