Group totals and grand total

[BDawg04]

I have a report that is broken down by state and then by county. I need to get a total number of complaints for each county and then a grand total of complaints at the end of the report. In the query there is a county code and a county name that corresponds to that code. The grouping is working fine. However, the totals for each group and the grand total are giving me fits. I looked around here and found the following code:

= Count([Afield])

The problem is I tried that code. There's always the possibility that I typed it wrong and overlooked something though. I tried placing the code in different footers with no luck. I tried to put the grand total in the report footer and it didn't work either. Anyone got any ideas? Thanks for anything you can give me.

 

[dhookom]

Is [Afield] an actual field name in your query? Did you use a field name from your query that will never be Null?

The expression:
=Count(*)
should work in any header or footer other than Page Headers and Footers.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BDawg04]

[AField] is just what was in the sample code. The field I want to count is counties. I tried to use:

=Count([CountyName])
also
=Count([CountyCode])

Neither one seemed to work. I am going to try them again just to see if I typed them wrong. I will also try =count(*) to see if I can get this working. Thanks for the help.

 

[SilentAiche]

BDawg04,

Where exactly are putting your formula? In the [blue]County Group Footer[/blue], =count([CountyName]) should work. And, in my testing at least, null values did not have any effect (they were not counted).

Duane - am I missing something obvious about the nulls (always a distinct possibility!)

Tim

 

[dhookom]

If you want to count the number of records in a group, you can use =Count([AnyNonNullField]). Using the field you are grouping on should always be a safe field to use except a group where the grouping field value is null.

BDawg04,
Make sure the name of the text box is not also the name of a field.
Also, when you state "Neither one seemed to work", you must be more specific. Did you get an error in the text box? Did you get the wrong count? Did your PC shut down? We can't see your screen so please be more specific so your issue can be cleared up more quickly.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BDawg04]

The first time I tried the report I used the wizard and used the summary options in the wizard. For some reason it came back with a total of 'yes'. I played with some things and I got 'error' in the text box where the total should be. I think I have it figured out now but if you have any more ideas I'll be more than happy to try them. Sorry if I seem a little quick with answers and vague but we are in the middle of an audit and it's a little busy around here. Lucky for me, I get to generate all the reports when they are requested. I haven't been on the job long and haven't had a lot of experience with Access so things are not real fun for me at the moment. Thanks for all of your help.