Sorting Headache

[PFC Graham]

We're a class of new Army Privates training to be 74Bs,
the Army's answer to computer support professionals.
we're practicing on Microsoft Access 97 and are wracking
our brains trying to find the answer to this simple,
stupid question:
how do you sort items in a field, (in a report, form, or
table) in some other way than alphabetical ascending and
descending?
we have a database of soldiers, and our rank structure is
PVT, PV2, PFC, SPC, CPL, SGT, SSG, etc. etc. and we'd like
to sort by rank, and specify what order to sort them in.
Help?

 

[Matthias Klaey]

We're a class of new Army Privates training to be 74Bs,
the Army's answer to computer support professionals.
we're practicing on Microsoft Access 97 and are wracking
our brains trying to find the answer to this simple,
stupid question:
how do you sort items in a field, (in a report, form, or
table) in some other way than alphabetical ascending and
descending?
we have a database of soldiers, and our rank structure is
PVT, PV2, PFC, SPC, CPL, SGT, SSG, etc. etc. and we'd like
to sort by rank, and specify what order to sort them in.
Help?

Use two fields, say RANK (String), and RANKNR (Integer). In the RANK
field, fill in your acronyms PVT, PV2, PFC etc. In the RANKNR field,
fill in the desired order of the String-Ranks as numbers, e.g., 1, 2,
3 etc, where 1 corresponds to PVT, 2 to PV2 etc.
Use the RANKNR field to sort the records on your form or report, but
display only the RANK field.

HTH
Matthias Kläy

 

[Gilligan]

First get the table designs correct.
Given the information you provided I would assume at least two tables:
tblSoldier
SoldierKey Autonumber Or whatever is the primary key
ServiceNumber
SurName
GivenName
BirthDate
Rank -> this would link to a lookup table.

tlkRank
Rank Text (entries would be PVT, PV2, PFC, SPC, CPL, SGT, SSG, etc)
(This would be the link field)
RankOrder Number
PayScale
Any other information specific to a rank

The contents of tlkRank would be something like
PVT 10
PV2 20
PFC 30
SPC 40
CPL 50
SGT 60
SSG 70

Your form or table would be based on a query such as
SELECT blah, blah, blah...
FROM tblSoldier INNER JOIN tlkRank
ON tblSoldier.Rank = tlkRank.Rank
ORDER BY tlkRank.RankOrder

Alternately, you could forget about creating a tlkRank table and write a
function to return the sort order. This approach is pretty sloppy.

Public Function RankOrder(strRank As String) As Integer
Select Case strRank
Case "PVT"
RankOrder = 10
CASE "PV2"
RankOrder = 20
Case "PFC"
RankOrder = 30
' you get the idea....
Case Else
RankOrder = 0
End Select
End Function

Finally, creating a second field to store the sort order in the soldier
table violates basic rules of data normalization and will eventually cause
maintenance problems. Every time you change a soldier's RANK you will have
to write code to make sure the RANKNBR is kept in sync with their current
RANK.

The first approach of creating a lookup table to store Rank specific
information is preferrable over all others. Note that in my sample I am
using the text string ("PFC") as the primary key of tlkRank and actually
storing that value to tblSoldier. An alternate approach would be to have
tlkRank structured as follows:

tlkRank
RankKey Autonumber -> this would be the link field
Rank Text ("PFC", etc)
RankOrder Numeric

and then tblSoldier might be
SoldierKey Autonumber
ServiceNumber
SurName
GivenName
BirthDate
RankKEY -> this would link to a tlkRank.RankKey

and the query would then be
SELECT blah, blah, blah
FROM tblSoldier INNER JOIN tlkRank
ON tblSoldier.RankKey = tlkRank.RankKey
ORDER BY tlkRank.RankOrder

Note that the only difference is in one case I am using an autonumber as the
primary key; in the other case I am using the Rank text value as the primary
key. There are advantages and disadvantages to both approaches.