How count number of times a string of characters appears in the detail section of a report

In the detail section of a report I have a field named "txtDescription".  The field's control source is "Description" coming from a query and the query data comes from a table named "txtProjectDetail".

The Description can come from any of the records entered in a table named “tblReprintCodes”.
In the report footer I want to print something like this…

Department #1 count:  12 records
Department #2 count:  6 records
Department #3 count:  0 records
Department #4 count:  9 records
Etc.

The “Department” needs to be some kind of loop that cycles through the available records in “tblReprintCodes”.

How can I accomplish this?

--Steve
SteveL13Asked:
Who is Participating?
 
GRayLCommented:
Try this:

SELECT tblReprintCodes.Description, Count([Description]) AS RespDept
FROM tblProjectDetail LEFT JOIN tblReprintCodes ON tblProjectDetail.ReprintCode = tblReprintCodes.Code
WHERE (((tblProjectDetail.ActualDropDate) Between [Forms]![frmSingleProjectReport]![txtStart] And [Forms]![frmSingleProjectReport]![txtEnd]))
GROUP BY tblReprintCodes.Description;
0
 
Patrick MatthewsCommented:
The easiest way would be to group your report on that description column, and get your counts in the section footer.  If you are using the report wizard that can be done for you automatically.

If you want that in the report footer, then add a subreport to the report footer, based on a query that uses an outer join to the tblReprintCodes table.
0
 
GRayLCommented:
What if the character string is contained two or more times in a given record - or is that impossible?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SteveL13Author Commented:
The character string would never be contained 2 or more time.  What I have (below) is working so far but if a "Department" had zero cases I want that department to appear with a "0" count.  How do I make that happen in the query?

SELECT tblReprintCodes.Description, Count(Nz([Description],0)) AS RespDept
FROM tblProjectDetail LEFT JOIN tblReprintCodes ON tblProjectDetail.ReprintCode = tblReprintCodes.Code
WHERE (((tblProjectDetail.ActualDropDate) Between [Forms]![frmSingleProjectReport]![txtStart] And [Forms]![frmSingleProjectReport]![txtEnd]))
GROUP BY tblReprintCodes.Description
HAVING (((tblReprintCodes.Description) Is Not Null));

--Steve
0
 
GRayLCommented:
Where is the Department in this query?
0
 
SteveL13Author Commented:
Its tblReprintCodes.  
0
 
SteveL13Author Commented:
That didn't work.
0
 
GRayLCommented:
So tell us the whole story.  I'm not here to pull teeth.  You display a Dept and a Count, yet only Description and Count() are in the query.  Go back and have another look at your question - does it make sense?
0
 
SteveL13Author Commented:
I think it makes sense.  But if a department (Description) has a count of "0" I want that department to show up with a count of "0".   I'm sorry if I'm not explaining well.
0
 
GRayLCommented:
What does 'That didn't work' mean - IOW what happened when you ran the query?
0
 
GRayLCommented:
Thanks, glad to help.  BTW, I guess you 'discovered' that the count of a field containing all nulls will in fact be 0.  Good luck with your project.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.