NickCrouch
asked on
Need custom sort in Access 2007 - sort by number (small to large), by 2 more values, then by remaining values.
I have a table and in one column I have various types of data that needs to be sorted.
Lets say I have:
1
3
2
bob
john
bob
greg
john
harry
greg
What I need is a way to custom sort the data so that it looks like this:
1
2
3
john
john
bob
bob
greg
greg
harry
So what I need is a custom sort that sorts first by number (smallest to largest), then by a second value, then by a third value, then by a fourth value, and then all remaining.
Of course these are example values, but the issue I am having is sorting the numbers, from small to large, and the text in the same column.
Any help would be appreciated!
I found the instructions below, but I can't figure out how to incorporate various numbers into this.
http://office.microsoft.com/en-us/access-help/sort-records-in-custom-order-HA010062658.aspx
1.Now, open the table in Datasheet view.
2.On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.
3.Add the fields you want, such as FirstName, LastName, and Title, to the grid.
4.Right-click the Field row in the first blank column, click Zoom, and then type the following expression:
IIf([Title] = "Vice President, Sales", 1, IIf([Title] = "Sales Manager", 2, IIf([Title] = "Sales Representative", 3, IIf([Title] = "Inside Sales Coordinator", 4, 5))))
Lets say I have:
1
3
2
bob
john
bob
greg
john
harry
greg
What I need is a way to custom sort the data so that it looks like this:
1
2
3
john
john
bob
bob
greg
greg
harry
So what I need is a custom sort that sorts first by number (smallest to largest), then by a second value, then by a third value, then by a fourth value, and then all remaining.
Of course these are example values, but the issue I am having is sorting the numbers, from small to large, and the text in the same column.
Any help would be appreciated!
I found the instructions below, but I can't figure out how to incorporate various numbers into this.
http://office.microsoft.com/en-us/access-help/sort-records-in-custom-order-HA010062658.aspx
1.Now, open the table in Datasheet view.
2.On the Home tab, in the Sort & Filter group, click Advanced and then click Advanced Filter/Sort on the shortcut menu.
3.Add the fields you want, such as FirstName, LastName, and Title, to the grid.
4.Right-click the Field row in the first blank column, click Zoom, and then type the following expression:
IIf([Title] = "Vice President, Sales", 1, IIf([Title] = "Sales Manager", 2, IIf([Title] = "Sales Representative", 3, IIf([Title] = "Inside Sales Coordinator", 4, 5))))
ASKER
@MadS - I tried to get that to work, but with no luck.
I am unable to get it to sort the numbers properly because they are sorted as text.
What happens is the numbers will sort to where a percentage such as 50% would show after 5%
so:
4.5%
40%
5.8%
50%
I thought about splitting into two seperate columns, but i am not sure how to combine back to one column in a query without manually copying the data.
I am unable to get it to sort the numbers properly because they are sorted as text.
What happens is the numbers will sort to where a percentage such as 50% would show after 5%
so:
4.5%
40%
5.8%
50%
I thought about splitting into two seperate columns, but i am not sure how to combine back to one column in a query without manually copying the data.
Have you used VAL function as specified in the expression? It is meant to convert text to value....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Found a workaround for the customer.
IIf([Title] = "Vice President, Sales", 100001, IIf([Title] = "Sales Manager", 100002, IIf([Title] = "Sales Representative", 100003, IIf([Title] = "Inside Sales Coordinator", 100004, VAL([Title])))))
where 100000 should be larger than your larger number.