counting

[Susie]

OK....I have a report with speakers and the number of
sessions they have spoken broken down by category. So I
may have 2-3 records for each speaker. The speaker name
has it's own header so that the name will appear only
once. In the detail section, I have the categories and
number of sessions. What I would like to do is count the
number of speakers. Since each speaker may have more than
one record, it is counting each record instead of each
speaker. Can you help me find a way to count only the
unique value (speaker's name) so that I can have an
accurate count of speakers. Thank you in advance for your
help. I hope this makes sense.

 

[Nikos Yannacopoulos]

Susie,

Here's a little trick to do it: create a textbox in the speaker header
section, set its ControlSource property to =1, and its Running Sum property
to Over All. This will give you a "serial number" on the speakers like 1, 2,
3 etc. and the number next to the last speaker will equal the total number
of speakers.
Assuming this textbox is named txtSpeakerCount, you can then create another
textbox in the report footer section, and set its ControlSource property to
=[txtSpeakerCount]. It will display the total number of speakers, and then
you can set the Visible property of txtSpeakerCount to No, if you don't want
it displayed in every speaker header.
If you want to display the total number of speakers in the report header
section, the process is similar but requires a line of VBA code in addition:
create txtSpeakerCount like before, then create an unbound textbox in the
report header section, named, say, txtTotalSpeakers. Then add the following
line of code to the report footer section's On Format event:

Me.txtTotalSpeakers = Me.txtSpeakerCount

The result of this is: when the report footer is formatted, by which time
all the speakers have been enumerated and txtSpeakerCount holds the desired
number, it assigns this value to the textbox at the header section.

HTH,
Nikos