Access Report Groups

[Justin]

I have three fields that i want to group by. I don't want the groups to sort
alphabetically. Instead I want them to be in order as in the query results.
How do I do this.

 

[Duane Hookom]

Can you describe the order in the "query results"? Why can't you duplicate
the order from the query results in the report sorting and grouping?

 

[Justin]

In the "grouping and sorting" toolbox it only lets you sort A to Z or vice
versa. Is there a property that I am over looking that looks at how the Query
is sorted.

 

[Duane Hookom]

Can you answer my questions rather than asking another question? I'm not sure
what sort order isn't addressed in A to Z (Ascending) or Z to A (Descending).

 

[Justin]

This is what i want the order to be. I am not sure if this is the right way
to go about doing this or not but this is what I want the order to be.
ORDER BY IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="Speacial
Subjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,IIf([subjects.category]="Science",7)))))));

 

[Justin]

Did I answer the question correctly?

This is what i want the order to be. I am not sure if this is the right way
to go about doing this or not but this is what I want the order to be.
ORDER BY IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="Speacial
Subjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,IIf([subjects.category]="Science",7)))))));

Can you answer my questions rather than asking another question? I'm not sure
what sort order isn't addressed in A to Z (Ascending) or Z to A (Descending).

 

[fredg]

This is what i want the order to be. I am not sure if this is the right way
to go about doing this or not but this is what I want the order to be.
ORDER BY IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="Speacial
Subjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,IIf([subjects.category]="Science",7)))))));

Can you answer my questions rather than asking another question? I'm not sure
what sort order isn't addressed in A to Z (Ascending) or Z to A (Descending).

You can add a new column to the query.

SortThis:IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="SpeacialSubjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,7))))));

Then in the Report's Sorting and Grouping dialog, sort the records by
this new field.

Note, you have sloppy spelling within the IIf statement (which surely
will cause the statement to error), and you only need 6 conditions, as
the 7th will be anything not listed in the previous 6.

Note: it would be more efficient, and allow you to change the sort
order easier, if you would create a LookUp table to indicate which
value to sort on, but that would be another post. Your IIfs, though
cumbersome, will work.

 

[Duane Hookom]

I agree with FredG regarding the solution and recommendation to store the
sort order in a table.

You have a couple other options:
1) place the horribly long IIf() expression directly in the Field/Expression
in the sorting and grouping:
=IIf(Subjects.Category="...."...)
2) create a small user defined function that accepts the category and
returns an integer value for the sort order. At least this would be easier to
maintain since it could be placed in a module of "modBusinessFunctions"

--
Duane Hookom
Microsoft Access MVP

This is what i want the order to be. I am not sure if this is the right way
to go about doing this or not but this is what I want the order to be.
ORDER BY IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="Speacial
Subjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,IIf([subjects.category]="Science",7)))))));

Can you answer my questions rather than asking another question? I'm not sure
what sort order isn't addressed in A to Z (Ascending) or Z to A (Descending).
--
Duane Hookom
Microsoft Access MVP

:

In the "grouping and sorting" toolbox it only lets you sort A to Z or vice
versa. Is there a property that I am over looking that looks at how the Query
is sorted.

:

Can you describe the order in the "query results"? Why can't you duplicate
the order from the query results in the report sorting and grouping?

--
Duane Hookom
Microsoft Access MVP


:

I have three fields that i want to group by. I don't want the groups to sort
alphabetically. Instead I want them to be in order as in the query results.
How do I do this.

You can add a new column to the query.

SortThis:IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="SpeacialSubjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,7))))));

Then in the Report's Sorting and Grouping dialog, sort the records by
this new field.

Note, you have sloppy spelling within the IIf statement (which surely
will cause the statement to error), and you only need 6 conditions, as
the 7th will be anything not listed in the previous 6.

Note: it would be more efficient, and allow you to change the sort
order easier, if you would create a LookUp table to indicate which
value to sort on, but that would be another post. Your IIfs, though
cumbersome, will work.

 

[Justin]

Thanks for the help. Sorting by the additional field works. I am however
going to look into using a look-up table because that is probably the "right"
way to do this.

Thanks again.

I agree with FredG regarding the solution and recommendation to store the
sort order in a table.

You have a couple other options:
1) place the horribly long IIf() expression directly in the Field/Expression
in the sorting and grouping:
=IIf(Subjects.Category="...."...)
2) create a small user defined function that accepts the category and
returns an integer value for the sort order. At least this would be easier to
maintain since it could be placed in a module of "modBusinessFunctions"

--
Duane Hookom
Microsoft Access MVP

This is what i want the order to be. I am not sure if this is the right way
to go about doing this or not but this is what I want the order to be.
ORDER BY IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="Speacial
Subjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,IIf([subjects.category]="Science",7)))))));

:

Can you answer my questions rather than asking another question? I'm not sure
what sort order isn't addressed in A to Z (Ascending) or Z to A (Descending).
--
Duane Hookom
Microsoft Access MVP

:

In the "grouping and sorting" toolbox it only lets you sort A to Z or vice
versa. Is there a property that I am over looking that looks at how the Query
is sorted.

:

Can you describe the order in the "query results"? Why can't you duplicate
the order from the query results in the report sorting and grouping?

--
Duane Hookom
Microsoft Access MVP


:

I have three fields that i want to group by. I don't want the groups to sort
alphabetically. Instead I want them to be in order as in the query results.
How do I do this.

You can add a new column to the query.

SortThis:IIf([subjects.category]="Characteristics of a Successful
Learner",1,IIf([subjects.category]="SpeacialSubjects",2,IIf([subjects.category]="Writing",3,IIf([subjects.category]="Reading",4,IIf([subjects.category]="Speacial
Subjects",5,IIf([subjects.category]="Social
Studies",6,7))))));

Then in the Report's Sorting and Grouping dialog, sort the records by
this new field.

Note, you have sloppy spelling within the IIf statement (which surely
will cause the statement to error), and you only need 6 conditions, as
the 7th will be anything not listed in the previous 6.

Note: it would be more efficient, and allow you to change the sort
order easier, if you would create a LookUp table to indicate which
value to sort on, but that would be another post. Your IIfs, though
cumbersome, will work.