Excel or Access

Question

What I am trying to do is make a form in Access and with out having to cut and paste over and over again have it ascend by rank, the problem with this is that lower ranks have letters closer to one if I have a list like this

Cpl 4th rank
MSgt 8th rank but it is a higher rank then Cpl
1st lt is is like the 11th ranking etc..
yes the 1st lt will usually go first but how would make it so that it shows up like
1st Lt
MSgt
Cpl

and there are other ranks out there. is there a module that i can use for this or would some one have to program something in visual basics for the spread sheet or for access. It is in database format but if I have to export it and do it in excell I can do that too.

Answer 1

The usual way to do this is to have a table of ranks and the rank sort order, you can use this to sort your table, the record source of your subform would be on the lines of:

SELECT Rank.Sort, MainTable.Rank FROM MainTable 
LEFT JOIN Rank ON MainTable.Rank=Rank.Rank

You need to include a number that says how the table is to be sorted.

Rank   Sort
1st Lt   1
MSgt     2
Cpl      3

A relational database, such as Access, only has an order if you give it one.

You probably have a table with a lot of people, I have called this MainTable in my example, then you should have a look-up table, let us call it Rank, and it should look like the table illustrated above. This means that you do not need two fields in the main table to say what the rank is and how it should be sorted, in addition, a look-up table means that each entry is the same, so you don't end up with, for example:

1st Lt
First Lt
1st Lieut

You might like to read Fundamentals of Relational Database Design

Answer 2

I really do not understand all of that can you please explain a little better like my comment I have already made a table called ranks what do you mean by sort order?

Answer 3

To put it another way, you need to do you ORDER BY statement on the field “Sort_order” field from your rank table and not by the name of the rank itself.

I would also recommend stepping that field in increments of 10 so if a new rank comes in you can put it in between 2 others without having to update the other records (ahhhh takes me back to my old VIC20 programming days with line numbers)

Answer 4

In your ranks table add a new field called “Sort_order”. The query would look something like this

SELECT tblPeople.Name, tblRanks.Rank_name, tblRanks.Sort_order FROM tblPeople INNER JOIN tblRanks ON tblPeople.Rank_ID = tblRanks.Rank_ID ORDER BY tblRanks.Sort_order DESC;

The table tblPeople contains the details of the people like name etc and the table tblRanks contains the names of the ranks along with a field telling the database what order to sort them in so Sergeant is above private