asked on

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

Patrick Matthews

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.

GRayL

What if the character string is contained two or more times in a given record - or is that impossible?

SteveL13

ASKER

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

GRayL

Where is the Department in this query?

SteveL13

ASKER

Its tblReprintCodes.

ASKER CERTIFIED SOLUTION

GRayL

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

That didn't work.

GRayL

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?

SteveL13

ASKER

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.

GRayL

What does 'That didn't work' mean - IOW what happened when you ran the query?

GRayL

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.