[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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))))

0
NickCrouch
Asked:
NickCrouch
  • 3
  • 2
1 Solution
 
MAdSCommented:
Try changing the expresion as below:

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.
0
 
NickCrouchAuthor Commented:
@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.
0
 
MAdSCommented:
Have you used VAL function as specified in the expression? It is meant to convert text to value....
0
 
NickCrouchAuthor Commented:
Was not able to get this to work with more than one column, but I ended up just using criteria to limit the view and then the customer didn't mind the numbers being out of order.  Thanks for the response though MadS!
0
 
NickCrouchAuthor Commented:
Found a workaround for the customer.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now