Access help: how to assign numeric values to non-numerics (for sorting)

[bigred]

For ex say I want to sort people by job title, but I want certain titles to be listed first .......eg Presidents listed first, then VPs, then Directors etc etc. Is there an easy way? Thx

[nimzov]

Can you not just add a numeric field (jobImportance) and sort on that field ?

nimzo

[bigred]

? I want to be able to sort on a given field which is not numeric. Maybe I'm not explaning this well.....

[six7s]

It's difficult to know how to pitch an answer when I don't know how much you know

Do you know how to add a field to a table?

Do you know how to assign a foreign key?

Can your write your own SQL queries?

[mummymonkey]

Like the others say it's hard to say what the best way is for you.

I would create another table (tblJobTitles) which has three fields.
A numeric field (RankJobTitle)
A text field (txtJobTitle)
And your unique ID (NumJobTitle)

Create an additional numeric field in your original table (NumJobTitle)
Create a one to many relationship between the NumJobTitle fields in each table.
If your original table is tblStuff then your Access SQL will be something like:

SELECT tblStuff.txtStuff, tblJobTitles.txtJobTitle
FROM tblJobTitles INNER JOIN tblStuff ON tblJobTitles.NumJobTitle = tblStuff.NumJobTitle
ORDER BY tblJobTitles.RankJobTitle

[Zax63]

Here is a link to the Microsoft knowledge base (http://support.microsoft.com/kb/304564) showing pretty much exactly what you are trying to do using a Switch function. I haven't tried it myself but it looks reasonable if there are not too many titles.
I've still got to agree with the others, the better way to go is a new table with titles and sort order.

[shadron]

As they all say above, you need to add a new column to your table of data - call it fldSortOrder, and make it a number-Long integer. Do this by right-clicking on the table's name on the Table tab and select "Design View". Then run a series of update queries from in Query-New for which the SQL looks like:

UPDATE table SET fldSortOrder = 1 WHERE fldJob = 'President';
UPDATE table SET fldSortOrder = 2 WHERE fldJob = 'VicePresident'
UPDATE table SET fldSortOrder = 3 WHERE fldJob = 'Director'

and so on, or just fill in ther values in the column by hand in datascreen view. When the values in the column are all in place, click on the fldSortOrder column header and select Sort - ascending.