Custom Sort on a report (1 Viewer)

[npa3000]

So I have, a report that shows all the customers and their total orders.

I want to custom-sort this report ( alphabetically, ascending order, descending order based on the total number of orders) without creating multiple queries and reports.

Is there any solution with VBA code?

Thanks

 

[theDBguy]

Can you show us some sample data to show the sorting order you want? Normally, you don't need to use VBA for that.

 

[Pat Hartman]

without creating multiple queries and reports.

In order to find the total number of orders, you need a query that will count them:

Select tblOrders.CustomerID, Count(*) As OrderCount
From tblOrders;

Then you would join this table to your tblCustomer to pick up everything else.


You cannot sort by something that is not in the report's RecordSource. You could of course use a dCount() but that would be extremely inefficient since the domain function will have to run a query for every single customer. The separate totals query is a significant improvement. If you don't want to make separate querydefs, you can add the totals query as a sub query but I would just keep the totals query separate and join to it for simplicity.

 

[CJ_London]

reports have their own sorting functionality, any sorting done in the recordsource will be ignored. Go to report design and click on the grouping and sorting option in the design ribbon to display the appropriate window.

Not sure what you are asking but implies if you have

customer..OrderTotal
A......................5
B.......................6
C......................4
D......................5

you want the order

B...6
A...5
D...5
C...4

in which case sort first by order total and then by customer name

 

[Pat Hartman]

I wasn't implying that the sort should happen in the recordset only that you cannot sort by something that is not in the recordset.

 

[npa3000]

I have a query that returns the clients and there total orders. That query is used by a report. And I want to create a custom "sorting filter" that sorts my data based on the client's name and the increasing order of orders.

It is possible with some trick or i have to create another report that sorts the data differently?

 

[CJ_London]

still doesn't make sense - I provided an example of what I think you mean - suggest you do the same

 

[Pat Hartman]

You can modify the sort order of the report in the Open or Load event. I'm not sure which. The Open runs before the recordset is loaded, the Load runs after so if you change the sort in the load event, Access has to resort the data that it has already sorted.