expression showing a count 1

[dna43]

i hope someone will understand this - I need to in a new text box use an expression to show the count(nbr) of works a composer has in a report. ie. bach, albeniza.

 

[dhookom]

Without any information regarding the record source (fields) or values in your report, this is a bit difficult.
In some reports, you could use a text box with a control source like:
=Abs(Sum([Composer]="Bach&quot )

Duane
MS Access MVP

 

[WayneRyan]

dna43,

I don't know what your report looks like, but there are a
couple of possibilities:

One is to create a query, where you group by composer and
include the count.

The second is, if hour report contains all of the detail
records, use the group footer and put a new control in
there that has the count.

Wayne

 

[dna43]

Here is what has to be done. Basicaly count there works.

composers and their work
composer name work
Albeniz count of work 1
Iberia

Bach count of work ?
Brandenburg Concerto No. 1 in F Major
Brandenburg Concerto No. 2 in F Major
Brandenburg Concerto No. 3 in G Major
Brandenburg Concerto No. 4 in G Major
Brandenburg Concerto No. 5 in D Major
Brandenburg Concerto No. 6 in B-flat Major
Christmas Oratorio
Mass in B Minor
Passacaglia and Fugue in C Minor
The St. Matthew Passion
The Well-Tempered Clavier
Toccata and Fugue in D Minor

Barber count of work ?
Adagio for Strings
Medea's Meditation and Dance of Vengeance
Overture to "The School for Scandal"

 

[dhookom]

If the Composers are grouped together then you can place a text box in either the Composer Header or Footer with a control source of:
=Count([Composer])

Duane
MS Access MVP

 

[dna43]

thanks, all for the help , solved the problems

 

[dna43]

then,
in a seperate report - how would one count the number for just the one composer.?
Bach count of work ?
Brandenburg Concerto No. 1 in F Major
Brandenburg Concerto No. 2 in F Major
Brandenburg Concerto No. 3 in G Major
Brandenburg Concerto No. 4 in G Major
Brandenburg Concerto No. 5 in D Major
Brandenburg Concerto No. 6 in B-flat Major
Christmas Oratorio
Mass in B Minor
Passacaglia and Fugue in C Minor
The St. Matthew Passion
The Well-Tempered Clavier
Toccata and Fugue in D Minor

 

[dhookom]

=Abs(Sum([Composer]="Bach&quot )

Duane
MS Access MVP

 

[cat02]

Interested in how you solved this problem. I can see how =count(composer) might work, but did it? Or did it continue to add the composers so that for Bach you got 13 counts of work instead of 12 and so on? Did you solve the second question? Did Duane's =Abs(Sum([Composer]="Bach&quot ) theory work?

 

[dhookom]

The best way to answer your questions is to make a simple test. YOu will find that =Count([AnyExpression]) will count all null values. My solution of =Abs( Sum([AnyExpression]) ) will return the total number of records where ANyExpression equals True.

Duane
MS Access MVP

 

[cat02]

So how do you make the expression only count the null values of each particular group?, I want it to continue to count all groups over the whole report but unfortunately in my test it continuously counts, i.e. if the first group has 3 values and the second group has 7 it counts 3 for the first and 10 for the second when I use the expression =count([Anyexpression]) Does this mean that the problem may lie with my query?
When I tested the tip for the second part of the question I found that my report would come up with #Error, again does this sound like a query problem?

 

[dhookom]

I meant that =Count([AnyExpression]) will count all NON-NULL values. To count the null values, use:
=Abs( Sum( IsNull([AnyExpression]) ) )
Make sure you don't have the Running Sum property set to Over All or Over Group.

Duane
MS Access MVP