Using Count in an Access Report 1

[Kiernan]

Ok, I have a report that lists all personnel in my work section, broken down by their office. My boss wants a count in the report for each office and a total for the entire section.

Something like this:

ABC
Smith, Joe
Carter, Aaron
2
DEF
Jones, Eric
1

Total Personnel: 3

I know that the count function is to be utilized to compute the values from a field, but I honestly have no idea how to form the expression, or where to put it.

Can you all, in your infinite wisdom, please help an Access newbie out?

 

[LarryDeLaruelle]

In the group footer or header band (or report footer/header)place an unbound text control. Note that the Count function will not work in the page footer/header. In that control, type this expression:

=Count([FieldName])

Replace [FieldName] with the name of any bound control that you are positive will contain a value. (The Count function will only count those records where the counted control has a value present). I prefer to use the key field when possible.

Hope this helps.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[CosmoKramer]

It looks like you already have a grouping defined in the report for office. In its group footer place a text box with this as its control source:

=Count(*)

To get a count of all of the records in the report you can copy & paste that same text box into your report footer.

That should do the trick.....

 

[Kiernan]

Thank you, so much! That worked perfectly!

 

[LarryDeLaruelle]

Cosmo:

That's neat; did not know you could use a wild card in the count. Learned something new today, thanks and have a star.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[gucciluv]

In my report I need to count the number of times a certain text appears in a feild. For example I have a database with a field called nationality in it. What I would like to do is in a report count the number of times each specific nationality appears in the nationality field such as -
Caucasian 10
Asian 5
Latino 8
Can you please tell me how to do this?

 

[CosmoKramer]

Check out Access help for the DCount function. Set up text boxes with the control source defined as something like this:

= DCount("[nationality]", "Your QueryName", "[nationality] = 'Caucasian'")

 

[richthep]

I want to put this in a group footer of my report, but when I use CosmoKramer's solution, I get the total for that field for the ENTIRE report. Is there a way to limit it to the group?

 

[CosmoKramer]

In its group footer place a text box with this as its control source:

=Count(*)

 

[richthep]

Unfortunately, that did not work. I've got a report that rates a customer's experience for different categories over a certain time period. When the report is run, the user enters a date range and it runs through the detail of the ratings, grouped by each restaurant location. At the end of the detail for each location, I want a "grid" that has the categories down the left side and the ratings at the top (i.e. "Excellent", "Poor", etc.). The "cells" are text boxes that should give a total of each type of rating for that store only. Your solution is giving me the total for all the locations. It looks like this:

Excellent Good Poor
Order Taker 12 7 2
Service 12 7 2
Product 12 7 2

Any ideas?

 

[CosmoKramer]

For this situation it seems like a crosstab report would be the best option. Insert this report as a subreport in your group footer and link them by location.

 

[JonoB]

Using Domain functions (i.e. DCount, Dmax, etc) too often is not a good idea...they tend to slow thigns down quite a bit.

The easisest solution for this is
=count(iif(nationality = Latino,1,0)
=count(iif(nationality = Asian,1,0)
etc,etc