Count a data field

[VRB03]

Listed below is a sample of a report I would like to count/sum the text field named status in access?

Project Project Program PM Approve Status Closed
Number Name Name Date Date
2001012 ERP Wave ES Jack 6/4/07 Active 07/23/07
2001012 ERP Wave ES Jack 5/3/07 Active 08/21/07
Total Active 3
2001012 ERP Wave ES Jack 5/7/07 Closed 08/05/07
2001012 ERP Wave ES Jack 5/5/07 Closed 07/23/07
Total Closed 2
2001012 ERP Wave ES Jack 5/4/07 Onhold 08/29/07
Total On Hold 1
2001012 ERP Wave ES Jack 5/4/07 Pending08/27/07
Total Pending 1
Total Status 7
* How to count/sum a text field in access and create an over all total

 

[jjonesal]

Are you trying to do this in an actual report or solely using code?

IMHO - the easiest way to do this if you are working with a report is to create a query that does the totals, such as:

SELECT Table1.ProjectNumber, Table1.Status, Table1.ProjectName, Table1.ProgramName, Table1.PM, Table1.ApproveDate, Table1.ClosedDate, Count(Table1.Status) AS CountOfStatus
FROM Table1
GROUP BY Table1.ProjectNumber, Table1.Status, Table1.ProjectName, Table1.ProgramName;

And then, base the report on that query.

If you need the code, I would use this query & create a querydef for it.

Hope this helps.

j

 

[Remou]

For future reference: forum703