Sorting by Grouping

[BobV]

I am trying to create a report that is an employee phone directory based off
my employee db. My problem is that it needs to be sorted in a particular
order that is not alphabetical. My groupings are first by Division; then by
Office Name. Under the Office Name, for each employee, I am printing their:

Office symbol
Position title
Name
Phone number

So first, to make it sort in the order I want it to, I added a column to the
WorkLocation table called ranking, and entered a numeric value for where I
want each one to fall. Works great, as long as all the office symbols under
a particular office name are the same. However, when I encounter an office
name that possesses numerous office symbols under it, it prints out each
individual as many times as there are different office symbols. For
example, for the division chief and his secretary, a symbol would be AC for
the DivChief and ACS for the Secretary; but they would both fall under the
heading of "Admin Division Chief"; and they each print out twice.

How would I make multiple office symbols under a single office name print
out properly? Any help would certainly be appreciated.

Thanks in advance,
Bob

 

[Ken Snell \(MVP\)]

Not sure that you need an extra field in the WorkLocation table. Can you not
just use the report's Sorting & Grouping property to sort the report in the
order you wish? In the Sorting & Grouping window, put Office Name as the
first field entry, then Office symbol as the second field entry, then
Position title as the third field entry, then Name as the fourth field
entry; this should let the report do the proper sorting by itself.

 

[BobV]

I can't really do that, because if I do, then it sorts alphabetically by
Division. There's a certain order, other than strictly alpha, that I need
them to appear.

 

[Duane Hookom]

If you want to set a sort order for a field or fields you must store a value
that can be used to determine the order. If you don't have a field that can
be used, you can create one.

An alternative is to write a function that accepts a field and returns a
value that can be used for sorting. However, you should maintain data, not
expressions.