counting status HELP!!!!!

[naomi]

Below is an example of the status count. How do I get a sum like the one below it in Reports?

AuditNumber Status Status Count
11587 IDO 1
11620 SR 1
11662 IDO 1
11679 SR 1
11759 IDO 1
11759 IDO 1
11759 IDO 1
11759 ZBAL 1
11759 ZBAL 1
11759 ZBAL 1
11759 ZBAL 1
11759 ZBAL 1
11767 ZBAL 1
11779 SR 1
11793 SR 1
11816 IDO 1
11816 ZBAL 1
11816 ZBAL 1
11816 IDO 1
11816 IDO 1
11816 IDO 1
11816 IDO 1
11816 IDO 1
11816 SR 1
11816 SR 1
11816 SR 1
11816 IDO 1
11816 ZBAL 1
11816 ZBAL 1
11816 ZBAL 1
11816 ZBAL 1
11816 IDO 1
11816 IDO 1
11816 ZBAL 1
11816 ZBAL 1
11816 ZBAL 1
11816 ZBAL 1
11816 SR 1
11816 SR 1

AuditNumber Status Total
11587 IDO 1
11620 SR 1
11662 IDO 1
11679 SR 1
11759 IDO 3
ZBAL 5
11767 ZBAL 1
11779 SR 1
11793 SR 1
11816 IDO 9
SR 5
ZBAL 10

 

[Fornatian]

You need a totals query summing status and grouped by status. The SQL would go something like:

SELECT MyTable.Status, Count(MyTable.Status) AS StatusCount
FROM MyTable
GROUP BY MyTable.Status;

Storing 1 in a separate field for counting is unrequired because we can count the instances of the Status field

 

[Pat Hartman]

Select AuditNumber, Status, Count(*) as StatusCount
From YourTable
Group by AuditNumber, Status;

You need to include AuditNumber. Count(*) is more efficient than Count(somefieldname) according to Microsoft.