MS Access Question - How to Sort

#1
Sub-Unit database in MS Access...

Want to sort by rank - Maj at the Top, Pte at the bottom...

Is there a way I can give 'MAJ' a value (like 1!) through all the ranks down to Pte, so that when I 'sort ascending' it comes out in rank order?

Grateful for all suggestions, tips....

Adjt
 
#2
use exel

easier to manipulate, user friendlier and you can colour code when things start to go out of date
 
#4
BIPOLAR77 said:
use exel

easier to manipulate, user friendlier and you can colour code when things start to go out of date
Fair point.

You can export to an excel spreadsheet quite easily.
 
#7
Excel is fine for nominal rolls and the like - but I'm actually trying to make a database that links to Outlook, has photos, etc, so Access is what I need...
 
#8
and where are you hoping to store this access database?

and who will have acccess to it?
 
#10
because you know it cant go onto diif onto a user account or webpage
 
#12
oh, and under the data protection act personnel have allowed you to store personal data, which i can only guess is a personal computer with or without local sy knowledge?
 
#13
I've had to create similar on a number of occasions especially when working in a TA unit that had less IT than my granny. The way I did it was by creating a table of all possible ranks, sometimes a corporal is a bombardier and a private is a rifleman etc. then use this as a drop down list in the main form or table. Assign each rank or rank group a value which remains hidden then sort on that value. Or if you want the rank value to be visible, use the NATO rank designators OR-1 to OR-9 and OF-1 to OF-9. Hope this helps.

Edited to add, you can do exactly the same in Excel.
 
#15
Adjutant said:
Excel is fine for nominal rolls and the like - but I'm actually trying to make a database that links to Outlook, has photos, etc, so Access is what I need...
I assume the photos are grading sheets and the like? Surely You could use separate documents and Hyperlinks etc?

Aside from the fact the MOD has not paid for MS Access licenses for a little while and you cannot have access on DII you are simply making a rod for your own back owing to the size of one huge database (with pictures etc) and the nightmare of backing it up let alone lots of people needing access (sic) hahahaha
 
#16
You can transfer Access databases if you meet certain criteria.

Esentialy you ned to justify the database (the need), proove its legal (data protection etc) and have a future exit plan (the need will ultimately be replaced by a tri service system etc)

Standard DII users have Access run time so databases can be used. If you can justify the criteria above you can also request a single user copy of full Access to maintain it.

If you don't meet all the criteria just try (1) being nice to your DII team when approaching transfer, or (2) get someone to copy it over afterwards. But that is cheating.

Some people suggest keeping legacy data on a standalone after dII is deployed but that is counterproductive as you have an old machine getting older, and potentially paying to support usupported equipment.


Prior to going on DII make sure the database is documented so the unit don't end up with an essential database getting bloated with nobody understanding it. Also keep it tested in runtime mode and compare the version of Access held with that licenced in DII. Avoid too many unnecessary addons that will conflict with what is available come DII.

Notethat Outlok integration and automation will be key in giving difficulties come dII. Complexibility, technology and security.

To answer the original question on sorting ranks consider a reference list. The actual rank details can then be valued.
 
#17
FFS! I have Access on my machine in my Office, I am playing around with a database... DII won't arrive here in the next 5 years and I'll be long gone by then!

Not bothered about DII issues, just how to get it to sort my table by ranks.

If you can help, love to hear from you. If you know too much about DII, Data Protection Acts and the like, you need to get out more!

Thanks,

Adjt
 
#18
tommikka said:
To answer the original question on sorting ranks consider a reference list. The actual rank details can then be valued.
Create a table of ranks - 01 to 09 for Private to WO1, and 11 to 19 for 2 Lt to FM, (in case war breaks out)

Then link the ranks in other tables to the rank reference table.

Sort aplha numericaly A-Z for low to high or Z-A for high to low.

And document it - 'long gone by then' is just the exact attitude that brings problems to the poor sod who has to sort out the database in 4 years time when your unsupported database has become both business critical and bloated.
 
#20
http://office.microsoft.com/en-us/access/HA100626581033.aspx

Sort records in custom order
Applies to: Microsoft Office Access 2007


There may be times when you want to sort data, not by alphabetical or numerical order, but by user-defined order (also called custom or logical order). For example, sorting weekdays so that Monday appears first makes more sense than sorting in alphabetical order, where Friday appears first. You can sort data in the Datasheet view of a table or query, or in the Form view of a form.
This should help.

And this definitely sounds like a job for access.

Unless you wanna setup up a LAMP server... ;)
 

Similar threads

Latest Threads