MS Access Question - How to Sort

Discussion in 'Army Pay, Claims & JPA' started by Adjutant, Nov 1, 2009.

Welcome to the Army Rumour Service, ARRSE

The UK's largest and busiest UNofficial military website.

The heart of the site is the forum area, including:

  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
     
  3. Fair point.

    You can export to an excel spreadsheet quite easily.
     
  4. plus when running prints on jpa its in excel anyway
     
  5. This is the link you want. It gives an example on how to sort employee rank;

    http://office.microsoft.com/en-us/access/HA100626581033.aspx

    Please don't use Excel to actually replace a database, those small spreadsheets have a habit of growing and turning into complete monsters that then need to be imported into Access to be of any use.
     
  6. 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...
     
  7. and where are you hoping to store this access database?

    and who will have acccess to it?
     
  8. That's my problem...
     
  9. because you know it cant go onto diif onto a user account or webpage
     
  10. Where I work, we're years away from DII(F)...
     
  11. 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?
     
  12. 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.
     
  13. Greenbaggy,

    That's what I'm after! How do I assign each rank a value that remains hidden, please?

    Thanks,

    Adjt
     
  14. 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