Custom Grouping on Report

Back on 11/23/05 (see thread below), "Dan M" posed a question regarding using
custom Groups in a report (other than ascending and descending). "Fredg"
responded with a solution that worked for Dan.

I have the same exact question as Dan. However, I can't make it work.

Fred said to add a new column to the query. Does this info get entered in
the "field" row, or where? Every time I enter it, I get an error. I've
tried a number of different things, but none work. For now, please just tell
me where to enter the "SortThis:[Status].Column(1)" statement in the query.
Then, perhaps, it will all fall into place.

Thank you.
Diane

"fredg" wrote:

On Wed, 23 Nov 2005 13:19:13 -0800, Dan M wrote:

I'm grouping on a "Status" field with the choices: Active, Archive, Final
Status, Inactive, On Hold, Post Operations. I want them in the order:
Active, Inactive, Post Operations, Final Status, On Hold, Archive. The
choices are in a drop down box.

"fredg" wrote:

On Wed, 23 Nov 2005 13:00:02 -0800, Dan M wrote:

I want my records grouped by a field but I want to sort them in the order I
want, not ascending or descending. It seems I'm stuck with just the two
choices.

What is the order you want? And how does Access know what it is?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


That's not going to work as you have it now.
Create a table
SortOrder Number datatype, Integer
Status Text datatype
TableName tblSortOrder

Enter each choice in the order you now have them.
Enter the SortOrder for each record in the order you wish it to sort
by in the report, i.e.
1 Active
6 Archive
4 Final Status
2 Inactive
5 On Hold
3 Post Operations


As rowsource for the Combo box:
Select tblSortOrder.Status, tblSortOrder.SortOrder from tblSortOrder;
Make the bound column column 1.
Set the Column count to 2
Set the column widths to
1";0"
Limit to List to Yes

In the query that is the report's record source add a new column:
SortThis:[Status].Column(1)

In the Report's Sorting and Grouping dialog, place the [SortThis]
field on the top row, sorting ascending.
Then place whatever other fields you wish to group by below it.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers.
--
Fred

Diane:

Fred suggested to Dan that he create a new table that had field called
Status in the table, you can't simply add the same name to your query.

Now lets assume that you have some values like Dan M (statuses) had that you
wanted to sort in a particular order. To do this, you as fred g
recommended, create a table that has two columns, the first with a byte or
integer field and the second with the values you want to sort. Then enter
the values in rows in the table in the order you want them sorted in your
query. e.g.

1 - Complete
2 - In Assembly
3 - Scheduled for Assembly
4 - Materials Ordered
5 - Order Entered

The idea is the text doesn't sort easily in the order you want it so you
create another index, in this case the byte or integer field in the table,
to drive the sort.

Then add this table to your query and join the text against whatever text
you have you need sorted in a particular order and then add the numeric
field as well.

In the report, sort or group on the numeric field noted above.

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


"DiHo" wrote in message
...
Back on 11/23/05 (see thread below), "Dan M" posed a question regarding
using
custom Groups in a report (other than ascending and descending). "Fredg"
responded with a solution that worked for Dan.

I have the same exact question as Dan. However, I can't make it work.

Fred said to add a new column to the query. Does this info get entered in
the "field" row, or where? Every time I enter it, I get an error. I've
tried a number of different things, but none work. For now, please just
tell
me where to enter the "SortThis:[Status].Column(1)" statement in the
query.
Then, perhaps, it will all fall into place.

Thank you.
Diane

"fredg" wrote:

On Wed, 23 Nov 2005 13:19:13 -0800, Dan M wrote:

I'm grouping on a "Status" field with the choices: Active, Archive,
Final
Status, Inactive, On Hold, Post Operations. I want them in the order:
Active, Inactive, Post Operations, Final Status, On Hold, Archive. The
choices are in a drop down box.

"fredg" wrote:

On Wed, 23 Nov 2005 13:00:02 -0800, Dan M wrote:

I want my records grouped by a field but I want to sort them in the
order I
want, not ascending or descending. It seems I'm stuck with just the
two
choices.

What is the order you want? And how does Access know what it is?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


That's not going to work as you have it now.
Create a table
SortOrder Number datatype, Integer
Status Text datatype
TableName tblSortOrder

Enter each choice in the order you now have them.
Enter the SortOrder for each record in the order you wish it to sort
by in the report, i.e.
1 Active
6 Archive
4 Final Status
2 Inactive
5 On Hold
3 Post Operations


As rowsource for the Combo box:
Select tblSortOrder.Status, tblSortOrder.SortOrder from tblSortOrder;
Make the bound column column 1.
Set the Column count to 2
Set the column widths to
1";0"
Limit to List to Yes

In the query that is the report's record source add a new column:
SortThis:[Status].Column(1)

In the Report's Sorting and Grouping dialog, place the [SortThis]
field on the top row, sorting ascending.
Then place whatever other fields you wish to group by below it.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers.
--
Fred

I guess I don't see where the [Status].Column(1) comes into
it.

Did you add the SortOrder column to the combo box's row
source table?

If you did, then you can sort the items in the combo box by
changing the combo box's RowSource to the query:
SELECT Status FROM statustable ORDER BY SortOrder

To sort/group a report, first add the SortOrder field to the
report's RecordSource query, then set the report's Sorting
and Grouping to use the SortOrder field.
--
Marsh
MVP [MS Access]


DiHo wrote:
Back on 11/23/05 (see thread below), "Dan M" posed a question regarding using
custom Groups in a report (other than ascending and descending). "Fredg"
responded with a solution that worked for Dan.

I have the same exact question as Dan. However, I can't make it work.

Fred said to add a new column to the query. Does this info get entered in
the "field" row, or where? Every time I enter it, I get an error. I've
tried a number of different things, but none work. For now, please just tell
me where to enter the "SortThis:[Status].Column(1)" statement in the query.
Then, perhaps, it will all fall into place.


"fredg" wrote:
That's not going to work as you have it now.
Create a table
SortOrder Number datatype, Integer
Status Text datatype
TableName tblSortOrder

Enter each choice in the order you now have them.
Enter the SortOrder for each record in the order you wish it to sort
by in the report, i.e.
1 Active
6 Archive
4 Final Status
2 Inactive
5 On Hold
3 Post Operations


As rowsource for the Combo box:
Select tblSortOrder.Status, tblSortOrder.SortOrder from tblSortOrder;
Make the bound column column 1.
Set the Column count to 2
Set the column widths to
1";0"
Limit to List to Yes

In the query that is the report's record source add a new column:
SortThis:[Status].Column(1)

In the Report's Sorting and Grouping dialog, place the [SortThis]
field on the top row, sorting ascending.
Then place whatever other fields you wish to group by below it.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers.


On Wed, 23 Nov 2005 13:19:13 -0800, Dan M wrote:
I'm grouping on a "Status" field with the choices: Active, Archive, Final
Status, Inactive, On Hold, Post Operations. I want them in the order:
Active, Inactive, Post Operations, Final Status, On Hold, Archive. The
choices are in a drop down box.


"fredg" wrote:
What is the order you want? And how does Access know what it is?


On Wed, 23 Nov 2005 13:00:02 -0800, Dan M wrote:
I want my records grouped by a field but I want to sort them in the order I
want, not ascending or descending. It seems I'm stuck with just the two
choices.

Marshall,
Thanks for your reply. It was simple and worked great ... to a point. Now
I'm stuck again. Here are the gory details:

My database is made up of several main tables and a number of "reference"
tables, which simply provide lists that are used in the main tables. I have
one table named "ProjectInfo". One of the fields in this table is
"ReportStatus". This field gets its values from a reference table named
"ReportStatusNames". Initially, "ReportStatusNames" included only one field
named "StatusName".

I then added another field to "ReportStatusNames" named "SortOrder". In the
RowSource for the "ProjectInfo" table, I entered "SELECT StatusName FROM
ReportStatusNames ORDER BY SortOrder", as you advised. The sorting worked
great in the combo boxes for the ProjectInfo table, the associated query, and
the associated form.

When I try to create the query upon which to base the report, that is where
I get stumped. I need the sort order to properly group the report.
Currently, the "ReportStatusNames" table is not one of the tables upon which
the query is based. Also, this table is not linked (in a relationship) to
the "ProjectInfo" table (it is simply used to define the values and order of
a field in this table). I don't know how to include the "SortOrder" field of
the "ReportStatusNames" table in the query without adding this table.
However, I can't add the table unless it is linked or joined to another table
(the "ProjectInfo" table, I assume). I have tried every combination I can
come up with, but always end up with an error.

I'm sure the solution is simple for those familiar with Access. But for me,
it's been frustrating and time-consuming trying to figure this out.

This one stumbling block is keeping me from creating this report. Can you
help please? If you need more details, please just let me know.

Thank you! Diane

--------------------------------

"Marshall Barton" wrote:

I guess I don't see where the [Status].Column(1) comes into
it.

Did you add the SortOrder column to the combo box's row
source table?

If you did, then you can sort the items in the combo box by
changing the combo box's RowSource to the query:
SELECT Status FROM statustable ORDER BY SortOrder

To sort/group a report, first add the SortOrder field to the
report's RecordSource query, then set the report's Sorting
and Grouping to use the SortOrder field.
--
Marsh
MVP [MS Access]


DiHo wrote:
Back on 11/23/05 (see thread below), "Dan M" posed a question regarding using
custom Groups in a report (other than ascending and descending). "Fredg"
responded with a solution that worked for Dan.

I have the same exact question as Dan. However, I can't make it work.

Fred said to add a new column to the query. Does this info get entered in
the "field" row, or where? Every time I enter it, I get an error. I've
tried a number of different things, but none work. For now, please just tell
me where to enter the "SortThis:[Status].Column(1)" statement in the query.
Then, perhaps, it will all fall into place.


"fredg" wrote:
That's not going to work as you have it now.
Create a table
SortOrder Number datatype, Integer
Status Text datatype
TableName tblSortOrder

Enter each choice in the order you now have them.
Enter the SortOrder for each record in the order you wish it to sort
by in the report, i.e.
1 Active
6 Archive
4 Final Status
2 Inactive
5 On Hold
3 Post Operations


As rowsource for the Combo box:
Select tblSortOrder.Status, tblSortOrder.SortOrder from tblSortOrder;
Make the bound column column 1.
Set the Column count to 2
Set the column widths to
1";0"
Limit to List to Yes

In the query that is the report's record source add a new column:
SortThis:[Status].Column(1)

In the Report's Sorting and Grouping dialog, place the [SortThis]
field on the top row, sorting ascending.
Then place whatever other fields you wish to group by below it.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers.


On Wed, 23 Nov 2005 13:19:13 -0800, Dan M wrote:
I'm grouping on a "Status" field with the choices: Active, Archive, Final
Status, Inactive, On Hold, Post Operations. I want them in the order:
Active, Inactive, Post Operations, Final Status, On Hold, Archive. The
choices are in a drop down box.


"fredg" wrote:
What is the order you want? And how does Access know what it is?


On Wed, 23 Nov 2005 13:00:02 -0800, Dan M wrote:
I want my records grouped by a field but I want to sort them in the order I
want, not ascending or descending. It seems I'm stuck with just the two
choices.

DiHo wrote:
My database is made up of several main tables and a number of "reference"
tables, which simply provide lists that are used in the main tables. I have
one table named "ProjectInfo". One of the fields in this table is
"ReportStatus". This field gets its values from a reference table named
"ReportStatusNames". Initially, "ReportStatusNames" included only one field
named "StatusName".

I then added another field to "ReportStatusNames" named "SortOrder". In the
RowSource for the "ProjectInfo" table, I entered "SELECT StatusName FROM
ReportStatusNames ORDER BY SortOrder", as you advised. The sorting worked
great in the combo boxes for the ProjectInfo table, the associated query, and
the associated form.

When I try to create the query upon which to base the report, that is where
I get stumped. I need the sort order to properly group the report.
Currently, the "ReportStatusNames" table is not one of the tables upon which
the query is based. Also, this table is not linked (in a relationship) to
the "ProjectInfo" table (it is simply used to define the values and order of
a field in this table). I don't know how to include the "SortOrder" field of
the "ReportStatusNames" table in the query without adding this table.
However, I can't add the table unless it is linked or joined to another table
(the "ProjectInfo" table, I assume). I have tried every combination I can
come up with, but always end up with an error.


Your idea of linking the tables is the correct approach. Do
you need hep with that? If so, please provide more details
about the tables, their primary and foreign keys and maybe
the existing query that doesn't quite work.

Once the query is adjusted to provide the report with the
SortOrder field, then use the report's Sorting and Grouping
window to specify a group on the SortOrder field.

--
Marsh
MVP [MS Access]

I did a simple join in the query (which I know I tried before) and it's
working great. Thank you for your help!

"Marshall Barton" wrote:

DiHo wrote:
My database is made up of several main tables and a number of "reference"
tables, which simply provide lists that are used in the main tables. I have
one table named "ProjectInfo". One of the fields in this table is
"ReportStatus". This field gets its values from a reference table named
"ReportStatusNames". Initially, "ReportStatusNames" included only one field
named "StatusName".

I then added another field to "ReportStatusNames" named "SortOrder". In the
RowSource for the "ProjectInfo" table, I entered "SELECT StatusName FROM
ReportStatusNames ORDER BY SortOrder", as you advised. The sorting worked
great in the combo boxes for the ProjectInfo table, the associated query, and
the associated form.

When I try to create the query upon which to base the report, that is where
I get stumped. I need the sort order to properly group the report.
Currently, the "ReportStatusNames" table is not one of the tables upon which
the query is based. Also, this table is not linked (in a relationship) to
the "ProjectInfo" table (it is simply used to define the values and order of
a field in this table). I don't know how to include the "SortOrder" field of
the "ReportStatusNames" table in the query without adding this table.
However, I can't add the table unless it is linked or joined to another table
(the "ProjectInfo" table, I assume). I have tried every combination I can
come up with, but always end up with an error.


Your idea of linking the tables is the correct approach. Do
you need hep with that? If so, please provide more details
about the tables, their primary and foreign keys and maybe
the existing query that doesn't quite work.

Once the query is adjusted to provide the report with the
SortOrder field, then use the report's Sorting and Grouping
window to specify a group on the SortOrder field.

--
Marsh
MVP [MS Access]

Steve,
It sounds like that's what I ended up doing that worked great. Thanks for
your advice!
Diane

"SA" wrote:

Diane:

Fred suggested to Dan that he create a new table that had field called
Status in the table, you can't simply add the same name to your query.

Now lets assume that you have some values like Dan M (statuses) had that you
wanted to sort in a particular order. To do this, you as fred g
recommended, create a table that has two columns, the first with a byte or
integer field and the second with the values you want to sort. Then enter
the values in rows in the table in the order you want them sorted in your
query. e.g.

1 - Complete
2 - In Assembly
3 - Scheduled for Assembly
4 - Materials Ordered
5 - Order Entered

The idea is the text doesn't sort easily in the order you want it so you
create another index, in this case the byte or integer field in the table,
to drive the sort.

Then add this table to your query and join the text against whatever text
you have you need sorted in a particular order and then add the numeric
field as well.

In the report, sort or group on the numeric field noted above.

--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg


"DiHo" wrote in message
...
Back on 11/23/05 (see thread below), "Dan M" posed a question regarding
using
custom Groups in a report (other than ascending and descending). "Fredg"
responded with a solution that worked for Dan.

I have the same exact question as Dan. However, I can't make it work.

Fred said to add a new column to the query. Does this info get entered in
the "field" row, or where? Every time I enter it, I get an error. I've
tried a number of different things, but none work. For now, please just
tell
me where to enter the "SortThis:[Status].Column(1)" statement in the
query.
Then, perhaps, it will all fall into place.

Thank you.
Diane

"fredg" wrote:

On Wed, 23 Nov 2005 13:19:13 -0800, Dan M wrote:

I'm grouping on a "Status" field with the choices: Active, Archive,
Final
Status, Inactive, On Hold, Post Operations. I want them in the order:
Active, Inactive, Post Operations, Final Status, On Hold, Archive. The
choices are in a drop down box.

"fredg" wrote:

On Wed, 23 Nov 2005 13:00:02 -0800, Dan M wrote:

I want my records grouped by a field but I want to sort them in the
order I
want, not ascending or descending. It seems I'm stuck with just the
two
choices.

What is the order you want? And how does Access know what it is?
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail


That's not going to work as you have it now.
Create a table
SortOrder Number datatype, Integer
Status Text datatype
TableName tblSortOrder

Enter each choice in the order you now have them.
Enter the SortOrder for each record in the order you wish it to sort
by in the report, i.e.
1 Active
6 Archive
4 Final Status
2 Inactive
5 On Hold
3 Post Operations


As rowsource for the Combo box:
Select tblSortOrder.Status, tblSortOrder.SortOrder from tblSortOrder;
Make the bound column column 1.
Set the Column count to 2
Set the column widths to
1";0"
Limit to List to Yes

In the query that is the report's record source add a new column:
SortThis:[Status].Column(1)

In the Report's Sorting and Grouping dialog, place the [SortThis]
field on the top row, sorting ascending.
Then place whatever other fields you wish to group by below it.

The nice thing about this method is that anytime you wish to change
the sorting order, all you need do is re-arrange the SortOrder
numbers.
--
Fred