How do I get a COUNT result to show up by itself on a report?

[deanstoney]

(Using Access 2007)
For a report I need to create and submit, they want counts of how many employees fit into a few given categories, and then they want some simple totals involving those count numbers.

I know how to create a query that returns the count number for a given category. I use the count function to count how many records are returned by the queries I've created to define the categories. That's easy.

However, when I try to enter a count query in the control source property of an unbound text box, it doesn't work, and I can't yet figure out some way of doing this.

I don't want to show the actual records returned by my category queries--I only want to show the count number, and I want to show counts for several queries.

Don't really care how I do it, I just want the count number in a box on the report.

Make sense? Please let me know if you need more information.

[Echidna]

Hi,

For the report, are you wanting a summary style ie
Category A 50
Category B 112
etc

or something more comprehensive like

Category A 50
Category A member 1
Category A Member 2
etc

Cheers

Leon

[deanstoney]

Summary style. Specifically what I need is:

Category A 5
Category B 3
Total A+B 8
Category C 2
Total above -C 6
Category D 1
Total above -D 5

Make sense?

[Echidna]

Hi

Hope this helps.

=DCOUNT("Employees","Employees","[EmpCategory]= 'CatA'")
=DCOUNT("Employees","Employees","[EmpCategory]= 'CatB'")
=DCOUNT("Employees","Employees","[EmpCategory]= 'CatA'") +
DCOUNT("Employees","Employees","[EmpCategory]= 'CatB'")

but that would be more for a form than a report though

You could separate the Category within the report as a group, and have a query for the source of the subreport, then having a second subreport for the second group of categories.

Something like

SELECT DISTINCT Category, COUNT(Category) AS CatCount
FROM Employees
GROUP BY Category
HAVING (Category = 'CatA') OR (Category = 'CatB')

Cheers

Leon

[deanstoney]

I figured it out! Or at least I found a solution. Hopefully there are better solutions, but I ended up creating a subreport for each of the numbers I needed, adding an unbound text box with a COUNT function in it at the bottom of each subreport, and then making all parts of the subreport (other than the COUNT text box content) invisible. Then I made each subreport really small and positioned it so the COUNT result would show up where I wanted it. This makes the Design view and Report Views a little crazy, but it prints out looking how I want it, with the data I want. Thanks for your help. And sorry I wasn't clear about my categories. When I said "category A", I didn't mean "Count every record from the Employees table that has a value of A in the category field." I actually meant something like: "Count how many records are returned by the query that queries which employees are x and have not done this since this date." But still, Thank you! And if you see an easier/simpler way, I'm still open for suggestions.
Thanks,
Dean

[TheSmileyCoder]

1.
Use the DCount function, simply write in the textbox, as its Controlsource:

=DCount("*","Tbl_Name","WHERE CLAUSE Without the Where word")

2.
Create a combox in your report (Don't worry the report doesn't show the dropdown button, looks just like a textbox)
As the recordsource, write your Count query, where the Count result should be placed in the first column.

[NeoPa]

Reports actually have the facility to handle aggregation within them at a report level (rather than relying on the underlying query to handle this).

If a TextBox in the detail section (which may be hidden if necessary) is called txtDtl, then TextBoxes in any of the header sections (Section Headers, Page Header or Report Header) can have the formula =Count([txtDtl]), and the value shown will reflect the count for the relevant data. Potentially different in each section.

[deanstoney]

Can you give a little more detail on this response #7. You referred to it on my other thread, but I'm not sure how to apply it. My report as a whole is unbound, because it pulls data from so many sources, and I've never been able to get =COUNT([field]) to return anything unless the data I wanted to count was also present on the same report. If I had a text box called txtDtl, what would it be bound to in order to get COUNT to return what I want?

[NeoPa]

The other thread mentioned is How do I get a COUNT to return a 0 on a report? btw, if anyone's interested in following the flow.

[NeoPa]

It would be bound to the item you wanted counted.

Let's be clear :
If the count you want is of native data, data available and already processed within the record source of the report (the bound table or query), and this data has a control bound to it, you can use the suggestion from post #7 referring to the other control internally (within the report object).

If not, it is foreign data, possibly in a table unrelated to anything you're already processing within your report, then Domain Aggregate functions (DCount() etc) are more appropriate, as the other has no way of working.

Which control you use in your report depends on your choices. I'm afraid I can't help you much there (I don't know what any are after all).