Create a specific sort order

[Access Joe]

Hi everyone,

Is it possible to create a specific sort order in a query that's not
necessarily alphabetic or numeric? For example, I want to sort a list of
items in a specifc way:

Laptop
Television
Briefcase
Jeans
Khakis

See - they aren't alphabetic in order, but I need to see the most popular
items first. Can I do this in Access?

Thanks so much!

 

[fredg]

Hi everyone,

Is it possible to create a specific sort order in a query that's not
necessarily alphabetic or numeric? For example, I want to sort a list of
items in a specifc way:

Laptop
Television
Briefcase
Jeans
Khakis

See - they aren't alphabetic in order, but I need to see the most popular
items first. Can I do this in Access?

Thanks so much!

Sure.
Add a new table to the database.
(This is the best way. You could use an IIf expression in the query,
but then anytime you wish to change the order, or add a new Value to
sort, you must re-do the query. With a table, all you need do is
change the number value of the field, as below.)

Create a table with just 2 fields.

SomeFieldName Text datatype (contains the value to sort, i.e.
Laptop, Television, etc.
SortOrder Number datatype, Integer (contains the wanted sort order)
TableName "tblSortOrder"

Enter each choice in the in any order. Then ...
Enter the SortOrder number value in the order you wish it to sort
by in the report, i.e.

1 Laptop
4 Jeans
2 Television
5 Khakis
3 Briefcase

In the query add the tblSortOrder to the query.
Set the relationship between the 2 tables as SomeFieldName Inner Join
on SomeFieldName.

Then add a new column:
[SortOrder]

Sort the query on this field, Ascending.
The above assumes each SomeFieldName value will be one of the 5 in the
tblSortOrder.

If you have more than the 5 possible choices above, and you don't care
how the other choices are sorted, change the relationship between the
2 field's to a Left Join.

Then change the query column to:
SortThis: IIf([tblSortOrder].[SortOrder] Is Null,9999,[SortOrder])

The first 5 will still sort as you wish, all the remaining records
will sort according to no particular order after the first 5.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers. No further design changes are needed in the query.

 

[KARL DEWEY]

I use the sort table but I have seen post on using Switch function.
--
KARL DEWEY
Build a little - Test a little

Hi everyone,

Is it possible to create a specific sort order in a query that's not
necessarily alphabetic or numeric? For example, I want to sort a list of
items in a specifc way:

Laptop
Television
Briefcase
Jeans
Khakis

See - they aren't alphabetic in order, but I need to see the most popular
items first. Can I do this in Access?

Thanks so much!

Sure.
Add a new table to the database.
(This is the best way. You could use an IIf expression in the query,
but then anytime you wish to change the order, or add a new Value to
sort, you must re-do the query. With a table, all you need do is
change the number value of the field, as below.)

Create a table with just 2 fields.

SomeFieldName Text datatype (contains the value to sort, i.e.
Laptop, Television, etc.
SortOrder Number datatype, Integer (contains the wanted sort order)
TableName "tblSortOrder"

Enter each choice in the in any order. Then ...
Enter the SortOrder number value in the order you wish it to sort
by in the report, i.e.

1 Laptop
4 Jeans
2 Television
5 Khakis
3 Briefcase

In the query add the tblSortOrder to the query.
Set the relationship between the 2 tables as SomeFieldName Inner Join
on SomeFieldName.

Then add a new column:
[SortOrder]

Sort the query on this field, Ascending.
The above assumes each SomeFieldName value will be one of the 5 in the
tblSortOrder.

If you have more than the 5 possible choices above, and you don't care
how the other choices are sorted, change the relationship between the
2 field's to a Left Join.

Then change the query column to:
SortThis: IIf([tblSortOrder].[SortOrder] Is Null,9999,[SortOrder])

The first 5 will still sort as you wish, all the remaining records
will sort according to no particular order after the first 5.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers. No further design changes are needed in the query.

 

[Access Joe]

Thanks Fred. I'll give this a try...and like the idea of not having to
change the query ever again. One question - would you mind showing me the
IIF expression you eluded to in the beginning of your response? I have a
feeling the client will want both options. Thanks again...this is great!

Hi everyone,

Is it possible to create a specific sort order in a query that's not
necessarily alphabetic or numeric? For example, I want to sort a list of
items in a specifc way:

Laptop
Television
Briefcase
Jeans
Khakis

See - they aren't alphabetic in order, but I need to see the most popular
items first. Can I do this in Access?

Thanks so much!

Sure.
Add a new table to the database.
(This is the best way. You could use an IIf expression in the query,
but then anytime you wish to change the order, or add a new Value to
sort, you must re-do the query. With a table, all you need do is
change the number value of the field, as below.)

Create a table with just 2 fields.

SomeFieldName Text datatype (contains the value to sort, i.e.
Laptop, Television, etc.
SortOrder Number datatype, Integer (contains the wanted sort order)
TableName "tblSortOrder"

Enter each choice in the in any order. Then ...
Enter the SortOrder number value in the order you wish it to sort
by in the report, i.e.

1 Laptop
4 Jeans
2 Television
5 Khakis
3 Briefcase

In the query add the tblSortOrder to the query.
Set the relationship between the 2 tables as SomeFieldName Inner Join
on SomeFieldName.

Then add a new column:
[SortOrder]

Sort the query on this field, Ascending.
The above assumes each SomeFieldName value will be one of the 5 in the
tblSortOrder.

If you have more than the 5 possible choices above, and you don't care
how the other choices are sorted, change the relationship between the
2 field's to a Left Join.

Then change the query column to:
SortThis: IIf([tblSortOrder].[SortOrder] Is Null,9999,[SortOrder])

The first 5 will still sort as you wish, all the remaining records
will sort according to no particular order after the first 5.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers. No further design changes are needed in the query.

 

[Michel Walsh]

ORDER BY Switch( item="Laptop", 1, item="Jeans", 4, item="television", 2,
item="khaki", 5, item='briefcase", 3, true, 6)



Since it is coded in ... the code, any modification implies access to the
code, and if the end user can change that part of the code, what else can he
changes, without telling you?... and you will have to supply the
maintenance? A table-base approach is much more reliable.


You can change the switch with a sequence of iif, even harder to maintain:

ORDER BY iif( item="Laptop", 1, iif( item="Jeans", 4, iif( ... ) ) )




Hoping it may help,
Vanderghast, Access MVP

Thanks Fred. I'll give this a try...and like the idea of not having to
change the query ever again. One question - would you mind showing me the
IIF expression you eluded to in the beginning of your response? I have a
feeling the client will want both options. Thanks again...this is great!

Hi everyone,

Is it possible to create a specific sort order in a query that's not
necessarily alphabetic or numeric? For example, I want to sort a list
of
items in a specifc way:

Laptop
Television
Briefcase
Jeans
Khakis

See - they aren't alphabetic in order, but I need to see the most
popular
items first. Can I do this in Access?

Thanks so much!

Sure.
Add a new table to the database.
(This is the best way. You could use an IIf expression in the query,
but then anytime you wish to change the order, or add a new Value to
sort, you must re-do the query. With a table, all you need do is
change the number value of the field, as below.)

Create a table with just 2 fields.

SomeFieldName Text datatype (contains the value to sort, i.e.
Laptop, Television, etc.
SortOrder Number datatype, Integer (contains the wanted sort order)
TableName "tblSortOrder"

Enter each choice in the in any order. Then ...
Enter the SortOrder number value in the order you wish it to sort
by in the report, i.e.

1 Laptop
4 Jeans
2 Television
5 Khakis
3 Briefcase

In the query add the tblSortOrder to the query.
Set the relationship between the 2 tables as SomeFieldName Inner Join
on SomeFieldName.

Then add a new column:
[SortOrder]

Sort the query on this field, Ascending.
The above assumes each SomeFieldName value will be one of the 5 in the
tblSortOrder.

If you have more than the 5 possible choices above, and you don't care
how the other choices are sorted, change the relationship between the
2 field's to a Left Join.

Then change the query column to:
SortThis: IIf([tblSortOrder].[SortOrder] Is Null,9999,[SortOrder])

The first 5 will still sort as you wish, all the remaining records
will sort according to no particular order after the first 5.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers. No further design changes are needed in the query.

 

[fredg]

Thanks Fred. I'll give this a try...and like the idea of not having to
change the query ever again. One question - would you mind showing me the
IIF expression you eluded to in the beginning of your response? I have a
feeling the client will want both options. Thanks again...this is great!

** snipped **


I could have sworn I answered this request a couple of hours ago, but
I don't see it yet. Perhaps I canceled the send. I'll try this again.
To sort using IIF..... (all on one line)

SortThis:IIf([SomeField]="Laptop",1,IIf([SomeField]="Television",2,IIf([Somefield]="Briefcase",3,IIf([SomeField]=
"Jeans",4,IIf([SomeField] = "Khaki",5,9999)))))

As you can see, it's quite clunky. If you wish to change the sort
order, or add an additional item to sort, you have to modify the
query.

I don't remember how many IIf's you can nest (more than 10 I'm sure),
but there is a limit.

If you use a table, all you need do is change the number values to
change the sort order. Add a new item to the list and it will also
sort how ever you wish it to.