Sort on 2nd group, not first

[Kim]

In my report, I would like it to skip sorting on the main group, but sort
everything based on the subgroup. Is this possible?

The top group is called "Division." The subgroup is called "Unit." I want
the whole report to sort only by the Unit.

 

[krissco]

In my report, I would like it to skip sorting on the main group, but sort
everything based on the subgroup. Is this possible?

The top group is called "Division." The subgroup is called "Unit." I want
the whole report to sort only by the Unit.

Sure. In design view of your report, click View/Sorting And Grouping.

You currently have Division and Unit listed and grouped.
Add a third line Unit - not grouped but sorted only. Move this line to
the top.

Ok. Now that that's out of the way, I'm not sure that the concept
makes sense to me. It must be an odd report.

-Kris

 

[Kim]

Hmm. Well, that kind of worked, but I still have a problem. Now it is showing
the Division header at the top of each group of Units. I don't want it to do
that. I only want to show the Division header once, at the beginning of all
the units that fall under it.

 

[krissco]

Hmm. Well, that kind of worked, but I still have a problem. Now it is showing
the Division header at the top of each group of Units. I don't want it to do
that. I only want to show the Division header once, at the beginning of all
the units that fall under it.

Sorry Kim. You have throughly confused me. It sounds like you are
trying to have your cake and eat it too. You can either group by
Division OR Units first. If one is first, the other can not be first.

Perhaps you can type out an example of what your report should look
like. . . for example, consider the following data:

(Division, Unit)
A, 1
A, 2
B, 1
B, 2
B, 3
C, 2
C, 3

The "solution" I posted earlier will give you:

A
1
B
1
A
2
B
2
C
2
B
3
C
3

What should the report look like?

-Kris

 

[Kim]

Here's what the report should look like:

Executive (Division)
Lottery Commission (Unit)
Position 1
Position 2
Directorate (Unit)
Position 1
Position 2
Legal/Contracts (Unit)
Position 1
Finance (Division)
Accounting (Unit)
Position 1
Position 2
etc.

Currently, after your instructions, it looks like this:

Executive (Division)
Lottery Commission (Unit)
Position 1
Position 2
Executive (Division)
Directorate (Unit)
Position 1
Position 2
Executive (Division)
Legal/Contracts (Unit)
Position 1
etc.

What I originally asked to do, if possible, is to not sort the first group
(Division), but to sort by the subgroup (Unit); therefore, the order of the
Division would be determined by the order of the Units.

Let me explain the actual problem. We have 7 Divisions. Each division has
numerous Units which are represented by a name and a number, eg:

Unit Name Unit #
Lottery Commission 100
Directorate 110
Legal 120
Accounting 221

All the 100s are in Executive, and all the 200s are in Finance, etc.
Whenever we list our Division names, they are not in alphabetical order, but
in the order of the 100 block of numbers that their Units represent. See
below:

Executive 100s
Finance 200s
CCD 300s
Security 400s
ITSD 500s
Sales 600s & 700s
RA 900s

This is the order that I'd like the Divisions listed in the report...not
their alphabetical order.

 

[krissco]

Here's what the report should look like:

Executive (Division)
Lottery Commission (Unit)
Position 1
Position 2
Directorate (Unit)
Position 1
Position 2
Legal/Contracts (Unit)
Position 1
Finance (Division)
Accounting (Unit)
Position 1
Position 2
etc.

Currently, after your instructions, it looks like this:

Executive (Division)
Lottery Commission (Unit)
Position 1
Position 2
Executive (Division)
Directorate (Unit)
Position 1
Position 2
Executive (Division)
Legal/Contracts (Unit)
Position 1
etc.

What I originally asked to do, if possible, is to not sort the first group
(Division), but to sort by the subgroup (Unit); therefore, the order of the
Division would be determined by the order of the Units.

Let me explain the actual problem. We have 7 Divisions. Each division has
numerous Units which are represented by a name and a number, eg:

Unit Name Unit #
Lottery Commission 100
Directorate 110
Legal 120
Accounting 221

All the 100s are in Executive, and all the 200s are in Finance, etc.
Whenever we list our Division names, they are not in alphabetical order, but
in the order of the 100 block of numbers that their Units represent. See
below:

Executive 100s
Finance 200s
CCD 300s
Security 400s
ITSD 500s
Sales 600s & 700s
RA 900s

Thank you Kim, that makes SO much more sense!

The way that I would choose to do this is to add a column to your
query:

select blah, blah, blah, left(UnitNumber, 1) as DivisionSort

Then group by DivisionSort, Unit and forget whatever else I have said.


-Kris