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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

GRayLCommented:
Where is the Department in this query?
0
SteveL13Author Commented:
Its tblReprintCodes.  
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.