Counting in reports

[Peter Allison (Wootton Bassett UK)]

I have a very simple database consisting of 3 tables - Volunteer details,
Client details and then a Record of Calls table that records which volunteers
have visited which clients and when.

I have built a report which uses a parameter query to give me details of
calls for a particular period - I just type the two dates in when prompted.
The report groups and sorts by volunteer, then by client - some clients get
more than one visit - and then finally sorts by date.

How can I count the number of individual i.e. different clients that a
particular volunteer has visited?

A rather unrelated question - when I type the first and last date into the
parameter query, is there any way I can display those two dates at the top of
the report? I am sure I have been shown but can't recall it!

Many thanks

Peter W Allison
Wootton Bassett, UK

 

[Allen Browne]

Open your report in design view.
Open the Sorting And Grouping dialog (View menu.)

From what you say, you have 3 rows in the dialog:
Volunteer Ascending
Client Ascending
VisitDate Ascending

For the Volunteer entry, you probably have Group Header set to Yes, so the
Volunteer appears above the client.
Set Group Header and Group Footer to Yes for Client as well.
Access adds the new sections to the report.
In the Client Group Header section place a text box with these properties:
Control Source =1
Running Sum Over Group
Name txtClientRS
Set the Visible property of the section to No if you don't wish to print
this.

Now in the Volunteer Group Footer, place a text box with Control Source of:
=[txtClientRS]

How it works:
The hidden text box accumulates 1 for each new client, and so gives a count
of distinct clients. The final text box then shows this total in the
Volunteer's group footer section.

For your final question, if you have a parameter named [Start Date] in your
query, you can refer to it in your report. Just use a text box with Control
Source of:
=[Start Date]

More information and an alternative approach here:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Peter Allison (Wootton Bassett UK)"