Conditional Counting in an Access Report

[gara742]

I'm using Access 2003 to try and get some industry standards coverage data summarized. I have a nifty little query already setup that counts how many times each standard clause is covered by an audit. In that same query a field holds a variable that tells the user looking at it which standard it's from (AS9100, CMMI, PMBOK, etc.).

What I'd like the report to do is to count the number of non-zero records, but only for a given standard type. For example if i had some records that looked like this:

ID, STD, Cov Count
1, A, 1
2, P, 0
3, C, 3
4, 2, C
then the report should return that the number of covered clauses for Standard type A is 1, for P it's 0, and for C it's 2. (sorry about the messy example data, this is being written from my home computer that doesn't have Excel or Access)

In essence I'm looking for something like the SUMIF function in Excel where the area summed and the area that's conditioned are not forced to be the same, as with the COUNTIF function.

I'm sure I could work around this with the addition of queries to split out the different standards, but my boss is looking to keep the number of queries and reports as low as possible.

Thanks for any help you can give.

[Stewart Ross]

Hi, and Welcome to Bytes!

There is no direct equivalent of SUMIF or the like in Access. SQL-based databases have no built-in concept of record position, so it is not possible to provide a SUM or COUNT which uses different ranges of the table to determine criteria and aggregation the way SUMIF and COUNTIF can do in Excel.

From what you have said, however, you should be able to use a fairly standard totals query in Access to do what you need. In the Access query editor add the base table or query containing the fields you showed in your post, turn on the totals (using View, Totals or by pressing the Sigma symbol on the toolbar), add the Std field (which will show as a Group By field). Add the count field you have already shown, and change the Group By for this field to Sum. Give the field another name (otherwise by default it will be SumofCov_Count) .

You are then summing the coverage count for each standard separately.

The SQL for this is just along the lines of

SELECT STD, Sum([Cov Count]) as T FROM [your query name]
GROUP BY STD;

-Stewart