Total appointments per agent

[MrClive]

Hi

I have agents booking appointments for customers, some face-to-face &
others by phone. The agents work in teams, and they're all stored in a
table with the Manager Name, Agent Name.

I have a seperate (main) table for customer & appointment data, with
data-entry via a form.

I need to be able to generate a report that will show the number of
appointments booked by each agent, grouped by Manager, between two dates
(start & end of month, for example).

I've got the query and it produces results but I get multiple rows
containing same data, as each agent is producing more than 1
appointment, e.g.:

Fred Davies Dave Jones
Fred Davies Dave Jones
Fred Davies Sue Morris
Fred Davies Sue Morris
Fred Davies Sue Morris

What I need is guidance as to how to get the query to count the number
of occurrences of each agents name, e.g.:

Fred Davies Dave Jones 2
Fred Davies Sue Morris 3

I'll be able to handle the report once the query is sorted.

Any help would be appreciated, thanks in advance
Clive

 

[John W. Vinson]

Hi

I have agents booking appointments for customers, some face-to-face &
others by phone. The agents work in teams, and they're all stored in a
table with the Manager Name, Agent Name.

I have a seperate (main) table for customer & appointment data, with
data-entry via a form.

I need to be able to generate a report that will show the number of
appointments booked by each agent, grouped by Manager, between two dates
(start & end of month, for example).

I've got the query and it produces results but I get multiple rows
containing same data, as each agent is producing more than 1
appointment, e.g.:

Fred Davies Dave Jones
Fred Davies Dave Jones
Fred Davies Sue Morris
Fred Davies Sue Morris
Fred Davies Sue Morris

What I need is guidance as to how to get the query to count the number
of occurrences of each agents name, e.g.:

Fred Davies Dave Jones 2
Fred Davies Sue Morris 3

I'll be able to handle the report once the query is sorted.

Any help would be appreciated, thanks in advance
Clive

Just use a totals query based on this query, or change this query into a
Totals query. Click the Greek Sigma icon (looks like a sideways M) and use the
"Group By" option on the two name fields; select some other field (preferably
the primary key of the "manyest" table in the query) and use the Count option
on the totals row.

Note that this forum is no longer supported by Microsoft; there are other
forums in my .sig that get a lot more activity, there are only a few of us old
fashioned types who even check this newsgroup.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:

http://answers.microsoft.com/en-us/office/forum/access?tab=question&status=all
http://social.msdn.microsoft.com/Forums/office/en-US/home?forum=accessdev
and see also http://www.utteraccess.com

 

[MrClive]

Just use a totals query based on this query, or change this query into
a Totals query. Click the Greek Sigma icon (looks like a sideways M)
and use the "Group By" option on the two name fields; select some
other field (preferably the primary key of the "manyest" table in the
query) and use the Count option on the totals row.

Note that this forum is no longer supported by Microsoft; there are
other forums in my .sig that get a lot more activity, there are only a
few of us old fashioned types who even check this newsgroup.

Thanks John - I checked recent posts before adding mine earlier