Sorting a Count, Totals Query - Unexpected Results

[Erehwon]

Hi,

I would like to know how to sort a calculated field in a report, so I can
see the person with the highest number of leads first.

From what I’ve read, I should create a totals query in order to link that
information and insert it into the report. I don’t know how to do this.
Attempts thus far have failed.

I’ve tried changing the Top value to 10% in the query, but it displays the
first seven entries of the query, only one of which is a top value.

Some background information –

The report draws on two queries: Production and Main Database

Production has the Salesperson name, their manager and the code number of
their lead.

The Main Database has all the stuff in Production, plus the details and
lacks the manager name. The two are linked by the code number of the lead,
which is unique.

The parameters are: Manager (from Production) - [Enter the name of the
Manager], Date when the Lead was created (from Main) - Between [Enter Start
Date:] And [Enter End Date:]

When I tried the totals query, I used COUNT in the Salesperson column to
determine the number of times the salesperson appeared. The result was that
in the datasheet of the query, a column called CountSalesperson was created
with the number 1 in every field. The Salesperson column was removed. This
was not what I expected.

So the solution was to just count the salesperson in the group footer of the
report, but now I can’t sort it.

Can anyone help me?

Thank you,

Erehwon

 

[Duane Hookom]

The first step is to avoid parameter queries
http://www.tek-tips.com/faqs.cfm?fid=6763. Once you are referencing controls
on forms for criteria, you can create a new, totals query that is very much
like your report's record source. It might look like:

SELECT SalesPerson, Count(*) as NumOf
FROM .....
WHERE [Date When the lead was created] Between Forms!frmDates!txtStart and
Forms!frmDates!txtEnd
GROUP BY SalesPerson;

Save this query and add it to your reports record source query and join the
Salesperson fields. You can then use NumOf to sort in your report.