Complicated Query for Military Ranks 1

[BullHalseyUSN]

The following query gets bits of information regarding all personnel in the "X" department and groups that information by DEPT and then by DeptHead.

DeptHead is a score from 1-3 where a Dept. Head=1, his/her top person=2 and then rank and file=3. In practice, there is always one DH, one head honcho, then a number of rank-and-file.

There is a small problem, however, in that, within the rank and file, personnel still need to be listed according to their rank, with the higher-ranking up top.

SELECT [NAME AND ADDRESS].SALUTATION, [NAME AND ADDRESS].Comment, TblDepartmentLookup.DeptName, [NAME AND ADDRESS].Extension, [NAME AND ADDRESS].PRD
FROM TblDepartmentLookup INNER JOIN [NAME AND ADDRESS] ON TblDepartmentLookup.DeptID = [NAME AND ADDRESS].DeptID
WHERE (((TblDepartmentLookup.DeptName)="FACILITIES&quot)
ORDER BY TblDepartmentLookup.DeptID, TblDepartmentLookup.DeptName, [NAME AND ADDRESS].DeptHead;

[NAME AND ADDRESS].SALUTATION is a concatention of a person's rank, his first name and last name. So we would have

MR2 M. Smith , for example.

In our world, ranks are faily easy to parse.

They are either a 3 (or, for the most senior enlisted, 4) character code. The first 2 are letters, and for the purposes of this discussion, can pretty much be ignored.

Examples of codes:
SKC
YN1
SK2
BM3

An "xxC" is a "Chief." An xx1 is a first-class and so on down.

chief
xx1
xx2
xx3

I need, nested within the foregoing query, a clause that orders the results by rank.

In plain english, the query would be something like this:

"Show me the various info about each member, order it by department, then by dept head, then, within those, show me a hierarchy according to rank where "xxC" is the highest and "xx3" is the lowest."

(i.e. I am in effect saying 'order according to the third character in [NAME AND ADDRESS].SALUTATION')

Can someone please offer guidance? Thanks!

Have a great Access day!
BH

 

[SgtJarrow]

You probably need to do something along these lines...

Instead of trying to sort the salutation, create an additional field for the query that lists only the third character of the rank. Run that through a custom function and return a nnumeric value, then sort on this value.

Field looks something like:

SortOrder: GetSortOrder(Mid([Name And Address].[Salutation],3,1))

Field Sort type is descending.

New Module with the following code (modify as necessary):

Public Function GetSortOrder(strInput As String) As Integer

Select Case strInput
Case "C": GetSortOrder = 4
Case "3": GetSortOrder = 3
Case "2": GetSortOrder = 2
Case "1": GetSortOrder = 1
End Select

End Function

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[BullHalseyUSN]

Interesting...I was actually hopeful of fixing this problem using only SQL. Thank you for your guidance.

 

[BullHalseyUSN]

How would one do this, please?

"Create an additional field for the query that lists only the third character of the rank."

I assume you mean parsing the rank so that what's returned is e.g. 1,2,3 or C?

 

[SgtJarrow]

Yes, exactly....if you are using typically Navy Rank codes...

Mid([Rank],3,1)

should give you the C,3,2,1



****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[BullHalseyUSN]

Thanks, pal. That Mid function is a neat trick. I guess it must be specific to Access...never seen it elsewhere.

I learned a lot here.

I didn't create a custom function, as it's not really necessary. I just added another field in Design view that parsed, then sorted, according to the third character.

 

[SgtJarrow]

Anytime.....we (prior army intel) military guys help ach other out...

****************************
When the human body encounters disease it raises its temperature making it uncomfortable not only for the body but also for the disease. So it global warming the Earth's way of saying we are not wanted?

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[raskew]

I know that you Navy guys get paid on the same scale as the rest of the military, and that's based on Pay Grade (E1 - E9) and time in service.

Write some code to tie the pay grade in, sort on pay grade, and it will all come out properly.

SKC = E-7
YN1 = E-6
SK2 = E-5
BM3 = E-4

Talk about a system that defies logic!

Best wishes,

Bob (SGM (E-9), USA (Ret))

 

[BullHalseyUSN]

Sargent Major -

Yep, I did that with another database. Just wanted a quick fix here.

The way I did it was to classify everyone as either an E or an O...never did figure out what to do with those Warrants, I guess (but either has the Navy hehe). That way I could also breakdown searches according to what officers/enlisted were doing.

Now if someone can just explain what the heck a Specialist is...

R/BH

 

[raskew]

Good point about the warrants. Can't remember what we use to do with them--probably disregard as much as possible. Hmm, that is a good point!

Re Specialist (Army-wise)--it used to be much different when you had SP4 (E-4) up to SP9 (E-9). Now, if you say Specialist, it's E-4, period! When they move to E-5, they become a Sergeant.

Best wishes,

Bob

 

[BullHalseyUSN]

Whoops! I misspelled "Sergeant."

Apologies. Thanks for the clarification on Specialists.

R/Ensign