Count by Grouped Item in Report 1

[kbdci]

I have a report that shows the number of products sold by each salesperson. The report is grouped and sorted by salesperson and I want to show how many salespeople are included in the report in the header of the report.

If I use count(*), I get the number of all of the records on the report (each product for each salesperson). How can I get the total number of salespeople (the grouped info on the report)? Thanks.

 

[randysmid]

Hi,
To put this into the header of the report, you can use the DCount or DSum function, depending on what is needed. Essentially, you would place a text box in the header, and using info from my FAQ, set up the criteria (aka Control Source) the same as your query. Here is the link to my FAQ: faq703-3066

You can simply replace "DSum" with "DCount", since the functions are almost identical. As you can imagine, DSum will add up the values using a specific field, such as hours worked, or sales total. DCount will count the specific number of records found that match a criteria, such as the number of salespeople in your report header. HTH,
Randy Smith
California Teachers Association

 

[kbdci]

Thanks. I was hoping that there was a simpler way that I was missing, similar to the Count() function to count all of the records. I'll try your suggestion.

Kathy

 

[randysmid]

Hi,
The problem is that the Access report doesn't know what the total will be until it reads, formats, and prints all the records. It is very much similar to driving between point A and point B. Sure, you can get a reliable estimate from Yahoo Maps, MapQuest, or something like that, but you won't get the exact mileage until you leave your driveway and get to the exact destination. Make sense? HTH,
Randy Smith
California Teachers Association

 

[CosmoKramer]

You might want to try this:

Create a query that is essentially a clone of the one you are using now; same criteria but:

Only display your salesperson field, and
Set the query to Group By that field.

In your report's report footer place the text box to hold the value. Set its control source to something like this, of course changing the names to reflect your own:

=DCount("[Salesperson]","YourQueryName")

One downside I can think of is that if the criteria changes on the report's main query, you'll have to remember to change it in this other query as well.....

Let me know how you make out.....

 

[randysmid]

Hi,
Kathy (kdbci) wants it to appear in the Report Header. HTH,
Randy Smith
California Teachers Association

 

[CosmoKramer]

Sorry....It should work in the header too...I meant header but typed footer....

stupid fingers

 

[PaulBricker]

Here is one other way to do what you need.
Put a textbox in your Group Header. Set the Control Source to =1, the Running Sum property to Over All and the visible property to No. Then put a textbox in your Report Header. In the Format Event for the Report Footer put
Me.ReportHeaderTextbox = Me.RunningSumTextbox

That will give you what you want.

Paul

 

[CosmoKramer]

Well done Paul....Now I've got to go back and change some of MY reports.....

 

[kbdci]

I want to thank you all for your suggestions. I will try all of the solutions and let you know which one I decide to use. I was really racking my brain over this seemingly simple problem.

Thanks again.

Kathy

 

[kbdci]

Just wanted to let you know that the solution from Paul Bricker works like a charm. It's just what I needed. Thanks again.

Kathy

 

[PaulBricker]

Glad that was a help.

Paul