By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

How do you define your own grouping method?

P: 14
Hello everyone,

I have a report with different grouping levels, and I can only select ascending or descending to group the 'Current Status' field in my report.

The problem is that I want the 'Current Status' field to be grouped in a particular order which does not fit into ascending or descending. For example, I want the following order:
1. Awaiting details
2. In progress
3. Closed

How do I get the report to be group in this order? I hope someone can please help.
Aug 7 '07 #1
Share this Question
Share on Google+
10 Replies


puppydogbuddy
Expert 100+
P: 1,923
Hello everyone,

I have a report with different grouping levels, and I can only select ascending or descending to group the 'Current Status' field in my report.

The problem is that I want the 'Current Status' field to be grouped in a particular order which does not fit into ascending or descending. For example, I want the following order:
1. Awaiting details
2. In progress
3. Closed

How do I get the report to be group in this order? I hope someone can please help.
You can do it easily, if you change the status description for "Closed" to someting like "Project Closed" so that the closed status will sort last in an alpha sort. Then all you have to do is this:
1. Using the sorting grouping tool, enter "status" ascending on the first row of the field area.
2. In the group properties section, select Group on: prefix characters.
3. For group interval: 1
Aug 7 '07 #2

P: 14
VSS
Sorry, that will not work for me because I can't change 'Closed' to 'Project Closed'. I wish it would be that easy!

Unfortunately, it has to say 'Closed'. Is there any other way of doing this please?
Aug 10 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Sorry, that will not work for me because I can't change 'Closed' to 'Project Closed'. I wish it would be that easy!

Unfortunately, it has to say 'Closed'. Is there any other way of doing this please?

Yes there is.
1. Place a textbox next to the textbox for Current Status. Name it txtSort.
2. Use the following expression in the control source for txtSort:
=IIf([Current Status] = "Awaiting details", 1, IIf([Current Status] = "In Progress", 2, 3))
3.Place txtSort as the first item in the sorting grouping tool,
Aug 10 '07 #4

P: 47
Here's a sample showing a quick and dirty solution to your problem using a UNION query in place of your original SELECT.
I have 3 values for ROLE in my TEAM_ASSIGNMENTS table: BU, OWN and TEAM. A normal sort will give:

1. BU
2. OWN
3. TEAM

By prefixing thesae with 1, 2, 3 I can change the sort order in a UNION query as follows

<code> SQL
SELECT "1" & [ROLE] AS SortField, * FROM TEAM_ASSIGNMENTS
WHERE (((TEAM_ASSIGNMENTS.ROLE)="TEAM"));
UNION
SELECT "2" & [ROLE] AS SortField, *
FROM TEAM_ASSIGNMENTS
WHERE (((TEAM_ASSIGNMENTS.ROLE)="BU"));
UNION
SELECT "3" & [ROLE] AS SortField, *
FROM TEAM_ASSIGNMENTS
WHERE (((TEAM_ASSIGNMENTS.ROLE)="OWN"))
</code>

If I group by Sortfield I will get the required result.

If yiu want to post the original query behind the report I can indicate the modifications required.

Good Luck!
Aug 10 '07 #5

P: 14
VSS
Hi Puppydogg,

I tried your method and unfortunately it does not work. All I get is the number '3' beside all 'closed' headings and the number '2' beside all 'in progress headings. Unless I am doing something wrong.

Thanks anyway.


Hi Barry,

I do't understand what you are telling me. I have used wizard to compose my report. Basically I want all 'closed' cases to be listed last in the group regardless of the other group headings.

My table name is: Report version 1
Report name: Report from January 2007 to 6 August 2007

Please help!
Aug 10 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
Hi Puppydogg,

I tried your method and unfortunately it does not work. All I get is the number '3' beside all 'closed' headings and the number '2' beside all 'in progress headings. Unless I am doing something wrong.

Thanks anyway.


Hi Barry,

I do't understand what you are telling me. I have used wizard to compose my report. Basically I want all 'closed' cases to be listed last in the group regardless of the other group headings.

My table name is: Report version 1
Report name: Report from January 2007 to 6 August 2007

Please help!
<<<I tried your method and unfortunately it does not work. All I get is the number '3' beside all 'closed' headings and the number '2' beside all 'in progress headings. Unless I am doing something wrong.>>>>

You did something wrong. Did you group and sort by txtSort?
Aug 10 '07 #7

P: 14
VSS
<<<I tried your method and unfortunately it does not work. All I get is the number '3' beside all 'closed' headings and the number '2' beside all 'in progress headings. Unless I am doing something wrong.>>>>

You did something wrong. Did you group and sort by txtSort?

Hi PuppyDog,

Thanks for you patience.

I have created a txt box next to my 'current status' txt box and called it txtSort under 'name' in properties.

I then entered the expression you done for me.

I then click on 'sorting and grouping' and under 'field/expression' I selct 'txtSort' and click on 'ascending'under 'sort order column. Then I drag the item to the top of my list in 'sorting and grouping.

The only thing that happens is that when I click on 'Preview' for my report I get a pop-up saying 'enter paarameter value'.

The order of the groupin does not change.

Thanks again for your help.
Aug 20 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
Hi PuppyDog,

Thanks for you patience.

I have created a txt box next to my 'current status' txt box and called it txtSort under 'name' in properties.

I then entered the expression you done for me.

I then click on 'sorting and grouping' and under 'field/expression' I selct 'txtSort' and click on 'ascending'under 'sort order column. Then I drag the item to the top of my list in 'sorting and grouping.

The only thing that happens is that when I click on 'Preview' for my report I get a pop-up saying 'enter paarameter value'.

The order of the groupin does not change.

Thanks again for your help.
You did not complete the final 2 steps:

1. sorting/grouping tool-go to group properties and set header = yes
2. report designer grid-place the txtSort textbox in the group header that was created by access when you set header = yes in #1 above.
Aug 20 '07 #9

P: 14
VSS
Hello Puppy Dog,

I done the final 2 steps that you recommended and I get '#error' on the report rather than the desired outcome.

what shall I do?

Thank you.
Aug 21 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
Hello Puppy Dog,

I done the final 2 steps that you recommended and I get '#error' on the report rather than the desired outcome.

what shall I do?

Thank you.
Leave the group header for txtSort in place, but drag txtSort down next to [status]. To eliminate the white space (gap) in the group header, drag the header bar down toward the detail bar so there is no gap.
Aug 21 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.