How to sort in a report by record count

[Jessica Stewart]

Hi there,

I have a database/report that tracks the number of invoices that have to be
sent back to departments.

My data is sorted by Department, then by Department Contact.

I have included a record count for the number returned to the Department in
Report form.

But I know want to sort my report so that the department with the highest
number of sendbacks is first, then descending in order (now, they are sorted
by Department name)

How can I accomplish this? Thank you !
Jessica

 

[Mr. B]

Jessica,

I may not have completely understood your issue, but if the record count for
the "send backs" that you have is actually one of the fields in your data
then you can add that field as the first Sort field for your report.

 

[John Spencer]

The only way to do this is to get the count into your query. You cannot
use a count calculated in the report to sort the data into an order.

You can use a report that just has the department and record count as
its source and then use a sub-report to get all the other data that is
in your current report.

You could get the count for each department and include that in your
query. Without more information on the structure of your current query
it is hard to give you a specific solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[KARL DEWEY]

If I understand correctly that you want the highest only listed then all of
the rest to be in alpha sort.
If you do not know subqueries then create a query like this --
SELECT TOP 1 Department, SendBack, 1 AS [MySort]
FROM YourTable
ORDER BY SendBack DESC;

Then use this query in your report query. Left join your table to the above
query on Department.
Add a calulated field --
SortAll: IIF([MySort] Is Null, 2, [MySort])

In report sort on SortAll and then Department.

 

[Jessica Stewart]

Thank you all for the information, it seems that I do need to get the record
count into the query, I will need to read up on how to do that.

Karl...I would like each department listed in order of the number of
sendbacks they have, putting the most 'common offenders' at the top of the
report for easy view by my supervisor. The outcome is to know which
departments need the most training.

If I understand correctly that you want the highest only listed then all of
the rest to be in alpha sort.
If you do not know subqueries then create a query like this --
SELECT TOP 1 Department, SendBack, 1 AS [MySort]
FROM YourTable
ORDER BY SendBack DESC;

Then use this query in your report query. Left join your table to the above
query on Department.
Add a calulated field --
SortAll: IIF([MySort] Is Null, 2, [MySort])

In report sort on SortAll and then Department.

Hi there,

I have a database/report that tracks the number of invoices that have to be
sent back to departments.

My data is sorted by Department, then by Department Contact.

I have included a record count for the number returned to the Department in
Report form.

But I know want to sort my report so that the department with the highest
number of sendbacks is first, then descending in order (now, they are sorted
by Department name)

How can I accomplish this? Thank you !
Jessica

 

[KARL DEWEY]

Ok, so just open report in design view, click on Grouping and Sorting and
select your 'record count' field descending.

Thank you all for the information, it seems that I do need to get the record
count into the query, I will need to read up on how to do that.

Karl...I would like each department listed in order of the number of
sendbacks they have, putting the most 'common offenders' at the top of the
report for easy view by my supervisor. The outcome is to know which
departments need the most training.

If I understand correctly that you want the highest only listed then all of
the rest to be in alpha sort.
If you do not know subqueries then create a query like this --
SELECT TOP 1 Department, SendBack, 1 AS [MySort]
FROM YourTable
ORDER BY SendBack DESC;

Then use this query in your report query. Left join your table to the above
query on Department.
Add a calulated field --
SortAll: IIF([MySort] Is Null, 2, [MySort])

In report sort on SortAll and then Department.

Hi there,

I have a database/report that tracks the number of invoices that have to be
sent back to departments.

My data is sorted by Department, then by Department Contact.

I have included a record count for the number returned to the Department in
Report form.

But I know want to sort my report so that the department with the highest
number of sendbacks is first, then descending in order (now, they are sorted
by Department name)

How can I accomplish this? Thank you !
Jessica