Is there any way to count the number of occurances in a field?

[HockeyFan]

I have a report that its record source is a query.
In the query I have a field called 'status'. In that field there could be 5 different possible text values.(these values came from a combo box on a form)
Is there a way for me to count the number of occurances of each value? The reason for this is because I have to show a total number of each value type on my report.

For example in my field called status, I could have the default(when no one has selected a value yet) of 'selectstatus'. the other choices are, 'monitoring', 'working resolution', 'deferred', or 'closed'.
Is there a way in my query, or in the report to calculate how many records have the value of 'working resolution' or how many have not selected a status yet, thus having the default value of 'selectstatus' for this field?

 

[mdav2]

A simple count and group would sort this unless I am missing something:

EG

Select status, count(status) from table group by status

Mark Davies
Warwickshire County Council

 

[HockeyFan]

well I need to count the occurances of each type of possible variables in that field. the possible choices in this field are 'selectstatus' (the default), the other choices are, 'monitoring', 'working resolution','deferred', or 'closed'.

 

[Gruuuu]

Did you try the query? Because that query should give you exactly the results you are asking for.
Expected results:

STATUS             COUNT(STATUS)
selectstatus       23
monitoring         12
working resolution 54
deferred           3
closed             98

 

[HockeyFan]

I did manage to get the query set up so It has a column that shows a count of each value type, however how would I go about collecting a total for each different value type? Would I be able to get a total for each value in a report somehow?
In my query, it shows approx 50 some records and I would like to keep them as individuals records and not put the grouping on them there, just to create totals within the query. Can I calculate the totals in the report for each value type?

 

[Gruuuu]

Are you saying that you want results somewhat like this?

STATUS             COUNT(STATUS) OTHERFIELD1 OTHERFIELD2
selectstatus       3             somevalue   somevalue
selectstatus       3             somevalue   somevalue
selectstatus       3             somevalue   somevalue
monitoring         1             somevalue   somevalue
working resolution 4             somevalue   somevalue
working resolution 4             somevalue   somevalue
working resolution 4             somevalue   somevalue
working resolution 4             somevalue   somevalue
deferred           1             somevalue   somevalue
closed             2             somevalue   somevalue
closed             2             somevalue   somevalue

If so, you need a query like so

SELECT status, statcount, otherfield1, otherfield2
FROM table JOIN (
   SELECT status, COUNT(status) 'statcount'
   FROM table) 'statuscount' 
ON (table.status = statuscount.status)

or something to that effect

 

[HockeyFan]

Gruuuu, that IS exactly how my query results look now.
What I would like to do now, is in my report to show the totals for each of those value types.
Using your example above:
I would like the report to say:

selectstatus 9
monitoring 1
working resolution 16
deferred 1
closed 4

 

[BigRed1212]

That's what mdav2 gave you on the first post.

 

[HockeyFan]

bigred, I need the query to output all of the fields just like in gruuuu's post, however on the report, I want to be able to have a total for each value.

The reason why I need it this way is because the source of the report, is the query, and there are many other fields on the report that are coming from this same query.

If I design the query so that it only gives the summary information totals, then I wouldn't have all of the field information for each of the records in the report.

 

[Gruuuu]

...so add the other fields to the select clause

SELECT status, COUNT(status), [i]your_other_field, yet_another_field, monkeys, terrifying_space_monkeys[/i]
FROM [i]yourtablename[/i]

Replace italicized areas with your field and table names

 

[HockeyFan]

everything is good in the query, I just need to figure out how to get the totals in the report. If i do totals in the query, then it won't output each individual record, and i need that information also. That's why i was trying to get totals in the report.

 

[Gruuuu]

Oh, sorry, forgot the GROUP BY.

SELECT status, COUNT(status), [i]your_other_field, yet_another_field, monkeys, terrifying_space_monkeys[/i]
FROM [i]yourtablename[/i]
[red]
GROUP BY status, [i]your_other_field, yet_another_field, monkeys, terrifying_space_monkeys[/i][/red]

 

[HockeyFan]

I actually do have group by right now. I feel that my query is working exactly like I would expect it would. here is my query. I need the query to continue to show the details for each record, but I need the grand totals for each of the category values. that's why I wanted to find a way within the report to total the number of each value.

SELECT tblProgramers.ProgrammerLastName, tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, LessonsLearnedTbl.Status, Count(LessonsLearnedTbl.Status) AS [Count], LessonsLearnedTbl.Project, LessonsLearnedTbl.WorkStreamName, tblProgramers.DirectMgr, [Forms]![reportsfrm]![manager] AS Expr1
FROM tblProgramers INNER JOIN LessonsLearnedTbl ON tblProgramers.PgmrId = LessonsLearnedTbl.PgmrId
WHERE (((tblProgramers.Active)=-1 Or (tblProgramers.Active)=-1))
GROUP BY tblProgramers.ProgrammerLastName, tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, LessonsLearnedTbl.Status, LessonsLearnedTbl.Project, LessonsLearnedTbl.WorkStreamName, tblProgramers.DirectMgr, [Forms]![reportsfrm]![manager]
HAVING (((LessonsLearnedTbl.Status)="selectstatus") AND ((tblProgramers.DirectMgr)=[Forms]![reportsfrm]![manager])) OR (((LessonsLearnedTbl.Status)="selectstatus") AND (([Forms]![reportsfrm]![manager])=0));

 

[Gruuuu]

ok ok ok.... I THINK I see now what you're saying. You're saying that you will need a single row for each record, but a TOTAL status count on the report?

If you are using the Access Reports feature, you can do this without including the count in your query at all. you just put a field somewhere with [=COUNT([status])] or something like that. That's really more of a question for the Access Reports Forum though:

http://tek-tips.com/threadminder.cfm?pid=703

I'm not very familiar with Access Reports

 

[HockeyFan]

That's exactly what I need.

 

[HockeyFan]

moved topic to report forum.