Need to refine sort order

[Scott_Brasted via AccessMonster.com]

Greetings,

I am working on a membership database. I have a table with member names, a
table with positions within the organization and a table of membership status.


The positions table has 4 fields in it. A primary key id field, a position
name field, a sub position name field and a numerical sort order field. The
table has less than 25 records in it and is unlikely to increase.

The position table has the list of positions such as president, vice
president, chief, 1st assistant chief, truck captain, etc. the sub position
field has a list of position categories such as exec, chief, truck, etc. and
the sort field is so I can get the chiefs to be in the order I want them
despite their titles or names. The chiefs need to be in a specific order
almost every time I use them.

So my problem today is that I have a roster query that contains the list of
members. The query is sorted by the position table's sort order field and
then member last names and filtered for only members whose status is active.
But it also needs to have the members whose position is one of four chiefs
listed at the end. That I have accomplished with this IIf statement -
Position: IIf([subPositionName]="Chief",[PositionName]," "). But their are
four other members whose records are affected by the chiefs being at the end
that I need to put back in alphabetical order. I do not know how to do that.

I am wondering if I could use another IIf statement to exclude all sort order
records except the ones for chiefs and put that in the Where clause after the
(((tblStatus.status)="Active")) part?

Here is the SQL for the query:
SELECT Trim([first] & " " & [last]) AS FullName, IIf([subPositionName]
="Chief",[PositionName]," ") AS [Position]
FROM tblPosName RIGHT JOIN (tblStatus INNER JOIN tblNames ON tblStatus.
statusID = tblNames.fkStatus) ON tblPosName.posID = tblNames.fkOffice
WHERE (((tblStatus.status)="Active"))
ORDER BY tblPosName.sortNumber, tblNames.last;

I'm sorry, I feel this is probably not very clear, but I cannot think of a
better explanation.

Best,
Scott

 

[Daryl S]

Scott -

You could add another field to your positions table for the ChiefSort, which
only has values for the Chief records, and the rest nulls. Then change your
sort to be on that field instead of the normal sort field.

Another option is to write a global function that returns the sort order you
want - it would return Nulls for all records except the Chiefs' records.

 

[Scott_Brasted via AccessMonster.com]

Daryl,

Many thanks. I had thought about adding another field, but I am always
worried that I am cluttering up my tables and that there might be a better
way. I will work on the global function, but that I am not very expert yet in
coding, so it will take me a while.

I appreciate the help.

Best,
Scott

Scott -

You could add another field to your positions table for the ChiefSort, which
only has values for the Chief records, and the rest nulls. Then change your
sort to be on that field instead of the normal sort field.

Another option is to write a global function that returns the sort order you
want - it would return Nulls for all records except the Chiefs' records.

Greetings,

[quoted text clipped - 38 lines]

Best,
Scott