group by field but not sorted ascending or descending

[Dan M]

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.

 

[fredg]

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?

 

[Dan M]

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.

 

[Rick Brandt]

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.

You'll need either an expression or a join to another table that will translate
those into a numeric or alpha character that will give you the sort you want.
The you can sort on that. The Switch() function for example could give you
this, but a join to a small translation table would be more efficient.

 

[fredg]

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.

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.

 

[Dan M]

Thank you, I'll give it a try soon.

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.

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.

 

[Dan M]

Ok, thanks, I'll see what I can do.

You'll need either an expression or a join to another table that will translate
those into a numeric or alpha character that will give you the sort you want.
The you can sort on that. The Switch() function for example could give you
this, but a join to a small translation table would be more efficient.

 

[Dan M]

Hey! Once I figured out what I was doing, it worked great!
Thanx

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.

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.

 

[DiHo]

Fred -
I had the same exact question as Dan, so I was happy so find your response.
However, I can't make it work.

You 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

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.

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.