number of events

[papa jonah]

I want to make a query that simply displays the number of times each
organization is mentioned in the records.
I have a database of records, each one of which has a organization
assigned. I want the query to say
org number
maint 25
ops 13
safety 45

The following seems to work However, I want to be able to add other
fields to limit the search based on criteria. As soon as I add any
others, I get a long list of repeated organizations that each have a 1
in the number column.

SELECT [Division group cross table].Division, Count(Division.[ORPS
Designator]) AS num
FROM [ORPS Data] LEFT JOIN (Division LEFT JOIN [Division group cross
table] ON Division.Div = [Division group cross table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]
GROUP BY [Division group cross table].Division;

 

[John Spencer]

If you are using the query grid, add hte additional fields you want to use
as criteria and change the GROUP BY to WHERE. This way the fields are not
part of the aggregation that takes place

The SQL would end up looking something like

SELECT [Division group cross table].Division
, Count(Division.[ORPS Designator]) AS num
FROM [ORPS Data] LEFT JOIN (Division LEFT JOIN
[Division group cross table] ON Division.Div = [Division group cross
table].[Group/Org]) ON
[ORPS Data].[ORPS Designator] = Division.[ORPS Designator]
WHERE [Orps Data].[Some Field] = "xyz" and [Orps Data].[someOtherField] =
"what?"
GROUP BY [Division group cross table].Division;

 

[papa jonah]

Thanks again John. Got that.
Now I have created a query that takes the result of this query and two
others to give me an overall snapshot of organizational health.
Now I have
org total miss concern
ops 25 4
maint 16 3 10
safety 24 1 1

where miss and concern are subsets of total.
This table is using right joins so that I get proper numbers from each
of the queries. I have also created a column to subtract miss and
concern from total for "the rest of them"
Events: Sum([qryd].[num]-[qryb].[Num 10a3s]-[qryc].[num 10a2s])

Here is my problem - the sum works as long as there are values in all
three columns.
In the above example, ops would be blank in this new column because
concerns is blank. Is there a way to insert a "zero" value in the
absence of data so that the expression column will work?

Thanks for all of your help.