Sorting Groups in a Report

[liz217]

I really appreciate any help on this one in advance.

I have a report, Bid Estimates. Within the report (run for one master
project), the group heading is Subprojects. Under each subproject is a list
of items & prices. Each Item has a list number. Looks like this:
Master Project
SubProject 1
List No. Item Price
SubProject 2
List No. Item Price

I want the report to sort by list number through out the subprojects. In
other words, SubProject 1 might have Items with List numbers 11-20 and
Subproject 2 might have Items with List numbers 1-10, so I would want
SubProject 2 to appear first.

At first glance, it looks like a design error in my database. But the real
life problem is that there are several master projects. And one might want
the first subproject on the report to be a water bid. And the next one might
want the water bid to be the last thing on the report. That is why I created
a field called list number.

I can force the sort in the way I name the subprojects (Base Bid A, etc.),
but I'd rather have it go solely off the list number, so there is no room for
error.

Thanks.

 

[Ken Snell [MVP]]

What is to happen if the list number in SubProject2 is 1, 3, 8, 14, and 18,
while the list number in SubProject1 is 13, 21, and 23? Or is that not
likely?

In the Sorting & Grouping list (View | Sorting & Grouping), you can select
the list number field to be the first one in the sort order, then have the
subproject field be the next sorting field.

 

[liz217]

Ken,

Thank you for your response. If I sort by List Number first, then I have a
subproject heading for each item individually resulting in several subproject
headings that are the same.

Let me see if I can give you an example to clear it up.

City A: Water & Sewer Bid (master project)

Water Bid (subproject)
1 (List Number) Item 10 $10
2 Item 2 $5

Sewer Bid (subproject)
3 Item 6 $1
4 Item 3 $2

The above would be for one report. If I wanted to run a report for City B.
It might look like this.

City B: Paving & General Construction

Paving
1 Item 12 $20
2 Item 13 $18

General Construction
3 Item 15 $10

Right now, it would put General Construction before Paving, because it is
sorted alphabetically. So the List number would be 3, 1, 2. But I want
Paving to be before General Construction. The only other thing I thought of
was to name the subproject in a way that it will sort alphabetically, like
Bid 1 - Paving.

Thanks for any suggestions you have.

Liz

 

[Ken Snell [MVP]]

You most likely will need to design a specific query that will contain
sorting field(s) in the way you want the report to be presented, and then
use that query as the report's recordsource.

The secret to doing this will be for you to "write out on paper" the sorting
logic that you want, based on the data that are available to you. In other
words, when you look at your data, you intuitively "see" how the data are to
be arranged. You need to translate this into the structure of a query and
report. (I know, this seems like a trite statement to make, but it's
actually what you need to do. Step back from your current report structure
and instead focus on your data from the tables. Look at them as raw data in
records, and figure out how they are to be sorted and grouped based on the
raw data.)

From your data, identify how you can "group" the data from the fields in
ways to arrange the data the way you wish to see it.

You say that you want the data to be ordered by list number, and to have a
project heading for the list numbers that are within a project. This assumes
that the list numbers within a project are not overlapping with the list
numbers from other projects. If this is a valid assumption, then perhaps you
can use a separate query to calculate the "minimum" list number associated
to a project and then use that value in a special sorting field. For
example, something like this, perhaps:

ListProjectSortingField: DMin("ListNumber", "TableWithProjectListNumbers",
"ProjectName='" & [ProjectName] & "'")

Then you can sort on ListProjectSortingField as the first sort field, then
the ProjectName as the second sort field, in the report. This may not get
you the final result you seek, but it may point you in the direction that
you want to go.

--

Ken Snell
<MS ACCESS MVP>

 

[liz217]

Thank you for your help. I wanted to sort the projects by the "Minimum List
Number". So I added a field to my table called MinSortNum. Then I sorted
the report by MinSortNum and grouped it by Project. It looks perfect!

Liz

You most likely will need to design a specific query that will contain
sorting field(s) in the way you want the report to be presented, and then
use that query as the report's recordsource.

The secret to doing this will be for you to "write out on paper" the sorting
logic that you want, based on the data that are available to you. In other
words, when you look at your data, you intuitively "see" how the data are to
be arranged. You need to translate this into the structure of a query and
report. (I know, this seems like a trite statement to make, but it's
actually what you need to do. Step back from your current report structure
and instead focus on your data from the tables. Look at them as raw data in
records, and figure out how they are to be sorted and grouped based on the
raw data.)

From your data, identify how you can "group" the data from the fields in
ways to arrange the data the way you wish to see it.

You say that you want the data to be ordered by list number, and to have a
project heading for the list numbers that are within a project. This assumes
that the list numbers within a project are not overlapping with the list
numbers from other projects. If this is a valid assumption, then perhaps you
can use a separate query to calculate the "minimum" list number associated
to a project and then use that value in a special sorting field. For
example, something like this, perhaps:

ListProjectSortingField: DMin("ListNumber", "TableWithProjectListNumbers",
"ProjectName='" & [ProjectName] & "'")

Then you can sort on ListProjectSortingField as the first sort field, then
the ProjectName as the second sort field, in the report. This may not get
you the final result you seek, but it may point you in the direction that
you want to go.

--

Ken Snell
<MS ACCESS MVP>

Ken,

Thank you for your response. If I sort by List Number first, then I have
a
subproject heading for each item individually resulting in several
subproject
headings that are the same.

Let me see if I can give you an example to clear it up.

City A: Water & Sewer Bid (master project)

Water Bid (subproject)
1 (List Number) Item 10 $10
2 Item 2 $5

Sewer Bid (subproject)
3 Item 6 $1
4 Item 3 $2

The above would be for one report. If I wanted to run a report for City
B.
It might look like this.

City B: Paving & General Construction

Paving
1 Item 12 $20
2 Item 13 $18

General Construction
3 Item 15 $10

Right now, it would put General Construction before Paving, because it is
sorted alphabetically. So the List number would be 3, 1, 2. But I want
Paving to be before General Construction. The only other thing I thought
of
was to name the subproject in a way that it will sort alphabetically, like
Bid 1 - Paving.

Thanks for any suggestions you have.

Liz