Custom Sort

Topic

New Reply

How to sort a field by a custom order (eg, Jan, Feb, Mar,…etc.)?

Armstrong

Reply | Quote

Replies

One option is to create a query based on the table and add a calculated column on which you can sort.

If that is not possible, create a new table with two fields: the field on which you want to sort and a number field SortOrder in which you enter the desired sort order. For example, if you have regions East, North, South, West which you want to sort as North, East, South, West, the table would look like this:

[table=Table for sort]

Region SortOrder East 2 North 1 South 3 West 4

[/table]
Create a query based on your table and on the new table, joined on the appropriate fields. Sort the query by the SortOrder field.

Reply | Quote

One option is to create a query based on the table and add a calculated column on which you can sort.

If that is not possible, create a new table with two fields: the field on which you want to sort and a number field SortOrder in which you enter the desired sort order. For example, if you have regions East, North, South, West which you want to sort as North, East, South, West, the table would look like this:

[table=Table for sort]

Region SortOrder East 2 North 1 South 3 West 4

[/table]
Create a query based on your table and on the new table, joined on the appropriate fields. Sort the query by the SortOrder field.

HansV,
Thanks for taking time to show me the tricks. Both had come across my mind. Your post should confirm both are widely used technique for custom sort. Thanks for your help.
Regards,
Armstrong

Reply | Quote