Change order of fields in a report to a custom arrangement

[*Glen*]

In a report, I want to list sections in a certain way that is not
alphabetical or numerical. Is there a way to do this? Curerntly, it is
alphabetical as below:

**Staff Section**

Chief of Staff
Command Counsel
G1
G2
G3
Public Affairs
Special Staff

** I would like for the fields to be organized as below:

***Staff Section***
G1
G2
G3
Chief of Staff
Command Counsel
Special Staff
Public Affairs

Thanks!

Glen

 

[golfinray]

Try going to Sorting and Grouping. It is one of your buttons on the command
bar. You should be able to set it anyway you wish.

 

[John Spencer]

You would need a table with the desired sort order as a field
Table: SortSection
SectionTitle: Your current staff titles
SectionSortOrder: a number field specifying the order you want items sorted
10, 20, 30, etc. (Doing the original input by tens makes it easier to change
the order without redoing all the records or to add a new Title in the middle
of the order)

Now add that table to your query (joining on the title). You can now use the
SectionSortOrder field to order your records.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[*Glen*]

I understand how to sort and group, but this case seems more advanced. How
can I sort or "tell" access the order which I want? Is there an expression I
must build when I click on sorting?

 

[KARL DEWEY]

Two ways to do it.
One is to add a field just for sorting at this level and enter numbers
representing your sort sequence.

Another is to use a calculated field in a query like this --
MySort: IIF([Staff Section] Like "G*", 1, IIF([Staff Section] Like "C*",
2, 3))
and second sort on [Staff Section] field using the report Sorting
and Grouping.

 

[*Glen*]

Thanks Karl!

Two ways to do it.
One is to add a field just for sorting at this level and enter numbers
representing your sort sequence.

Another is to use a calculated field in a query like this --
MySort: IIF([Staff Section] Like "G*", 1, IIF([Staff Section] Like "C*",
2, 3))
and second sort on [Staff Section] field using the report Sorting
and Grouping.

--
Build a little, test a little.

In a report, I want to list sections in a certain way that is not
alphabetical or numerical. Is there a way to do this? Curerntly, it is
alphabetical as below:

**Staff Section**

Chief of Staff
Command Counsel
G1
G2
G3
Public Affairs
Special Staff

** I would like for the fields to be organized as below:

***Staff Section***
G1
G2
G3
Chief of Staff
Command Counsel
Special Staff
Public Affairs

Thanks!

Glen

 

[*Glen*]

THANKS JOHN! It worked like a charm...and was not too complicated