Sorting in a Report

[chastst]

I am working on a report that groups data by a particular field. Then I count how many entries for each field. Is there a way to sort the report before it is displayed by descending order of the count of entries?

Thanks

 

[chrismcbride]

Create a query that collects all the information you need for the Report and include a Sum(field you want to sort on). Set the report's control source to this query and you should have this field available to sort or group on.
Good Luck
Chris

 

[chastst]

I didn't understand your answer. I am trying to count the number of times some equipment has been worked on, and then output the report sorted in descending order of occurences. The count can be on any of the fields in the query or report, it is just a count of "hits". All of the fields are text fields and dates.

Thanks

 

[Pat Hartman]

Since the count is not calculated until the report is printed (or displayed in preview mode), it is not possible to sort by the count because Access doesn't know what it is ahead of time. The recordset is always sorted before the report step.

The solution is to create a query that includes only the break fields and a Count(*) function. Then join this query to your existing query, pulling in the count field. You will now be able to sort by the count field because you have calculated it ahead of time.

I'm going to make up an example because your question gives no clue as to what your application is. Assume you have a report that lists order summaries for customer.

countquery:
Select CustomerID, Count(*) AS CountOfOrders
From OrderTable
Group By CustomerID;

yourPresentquery:
Select CustomerID, OrderID, OrderDate, OrderTotal
From OrderTable;

newversionofyourpresentquery:
Select O.CustomerID, O.OrderID, O.OrderDate, O.OrderTotal, Q.CountOfOrders
From OrderTotal AS O Inner Join countquery AS Q On O.CustomerID = Q.CustomerID
Order by Q.CountOfOrders, O.CustomerID, O.OrderID;

 

[Pat Hartman]

Sorry, take the order by clause out of the final query. You need to specify sort sequence in the report's sorting and grouping options. The sort sequence of the query is ignored by the report writer so don't waste the time with an order by clause. That will just cause Access to sort the file twice.