Count Function

[Tom]

Is it possible to apply criteria to a count function. I
would prefer not to use the DCount function.
I have a table with Item Id [Number], which are grouped in
ID [System]and given a [Priority]. I want to group by
[System] and build expresions to count the number of each
[Priority]. I know there are CountIf functions in excel
but am unsure how to perform this operation in Access.

 

[Joan Wild]

I'm uncertain of your field names, but open a new query with the needed
tables. Go to View, Totals.
Add your [system] field (it will automatically choose Group By in the Totals
row)
Add the [Priority] field (again Group By)
Add the [Priority] field again and then choose Count in the Total Row of the
grid.

 

[david epsom dot com dot au]

One method is to use IIF in your query field to
choose a value of 1 or 0. The IIF critera does
not have to be the same as any sorting or grouping
criteria, which allows you to group by multiple
overlapping criteria in just one query.
Sum the counted values, so you don't have to
select and count.

v1: IIf([priority]=1,1,0)
v12: IIf(([priority]=1) or ([priority]=2),1,0)

(david)

 

[Tom]

Thanks,
That should give me what I'm looking for. I appreciate it.

-----Original Message-----
One method is to use IIF in your query field to
choose a value of 1 or 0. The IIF critera does
not have to be the same as any sorting or grouping
criteria, which allows you to group by multiple
overlapping criteria in just one query.
Sum the counted values, so you don't have to
select and count.

v1: IIf([priority]=1,1,0)
v12: IIf(([priority]=1) or ([priority]=2),1,0)

(david)

Is it possible to apply criteria to a count function. I
would prefer not to use the DCount function.
I have a table with Item Id [Number], which are grouped in
ID [System]and given a [Priority]. I want to group by
[System] and build expresions to count the number of each
[Priority]. I know there are CountIf functions in excel
but am unsure how to perform this operation in Access.

.