I would like to count the total occurances of a field

[HockeyFan]

I have a query and it is doing just as I want it to. It displays each record, and also has a field called 'status' that can have five different values.
The possible choices in this field are 'selectstatus'(the default), the other choices are, 'monitoring', 'working resolution','deferred', or 'closed'.
I have a count field in my query that puts a number of how many records have occurances of that value. The query output looks something 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

What I want to do is on my report to simply total up the field called 'count', for each of the different value types. For example, I would like the report to say:
selectstatus 9
monitoring 1
working resolution 16
deferred 1
closed 4

 

[dhookom]

Change the record source to be a totals query like:

SELECT Status, Sum([Your column name]) as TheCount
FROM [doing just as I want it to]
GROUP BY status;

If you can't figure this out, provide real column and query names or your SQL view of "doing just as I want it to".

Duane
Hook'D on Access
MS Access MVP

 

[HockeyFan]

I was thinking that If I create sums in my query, then it would not show each individual record in the query. I want it to continue to show each individual record in the query becauase I use the detailed record information in my report also. That is why I was just hoping to find a way to total each of the values in my report. Her is my query sql anyhow.

SELECT tblProgramers.ProgrammerLastName, tblProgramers.ProgrammerFirstName, tblProgramers.PgmrId, LessonsLearnedTbl.Status, Count(LessonsLearnedTbl.Status) AS [Count], LessonsLearnedTbl.Project, LessonsLearnedTbl.WorkStreamName, tblProgramers.DirectMgr, [Forms]![reportsfrm]![manager] AS Expr1FROM tblProgramers INNER JOIN LessonsLearnedTbl ON tblProgramers.PgmrId = LessonsLearnedTbl.PgmrIdWHERE (((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));

 

[MajP]

You can use what Duane provided as the recordsource for a subreport that follows in a footer.

 

[HockeyFan]

I've never done that before. I've done subforms, so I'm assuming it's a similar concept.
So would I create a whole new query such as duane suggested above, and then use that as the record source for the few fields that I need sub totals on?
And I'm also assuming they will be linked by some field in the report that will keep the records in sync with the main report...
I'll give it a try. thanks for the suggestion.

 

[HockeyFan]

It sounded so simple, but I'm having a lot of trouble creating this new query. My original query with all of my field names is 2 posts up. Are you able to give me a little assist please?

 

[MajP]

Do just what Duane Said, and make your original a saved query. That is going to make this a whole lot easier! Lets assume it is called qryStatus. Here is a more readable format. I would give a better name then Expr1 for [Forms]![reportsfrm]![manager]. I would not call Count(LessonsLearnedTbl.Status) as Count. Count is a reserved word call it StatusCount

qryStatus

SELECT 
 tblProgramers.ProgrammerLastName, 
 tblProgramers.ProgrammerFirstName, 
 tblProgramers.PgmrId, 
 LessonsLearnedTbl.Status, 
 Count(LessonsLearnedTbl.Status) AS [StatusCount], 
 LessonsLearnedTbl.Project, 
 LessonsLearnedTbl.WorkStreamName, 
 tblProgramers.DirectMgr, 
 [Forms]![reportsfrm]![manager] AS Expr1
FROM 
 tblProgramers 
INNER JOIN 
 LessonsLearnedTbl 
ON 
 tblProgramers.PgmrId = LessonsLearnedTbl.PgmrIdWHERE (((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))

So simply

Select 
 Status, 
 sum(StatusCount) AS SumStatus
FROM
  qryStatus
GROUP BY
  Status

 

[HockeyFan]

Most excellent MajP, now I understand. Use my original query and then get the subtotals from that one in a separate query. Thank you very much for your assistance.

 

[dhookom]

I want it to continue to show each individual record in the query becauase I use the detailed record information in my report also.

Then you need to use a subreport based on the totals query. Add the subreport to the Report Footer (or Report Header) section.

Next time, you should make it clear in your first post exactly what you want to do like:
"I need to display this detailed information in my report as well as display totals by status at the end of the report."

Duane
Hook'D on Access
MS Access MVP