'Sorting By' on Reports

  • Thread starter Thread starter Jacob Frankham
  • Start date Start date
J

Jacob Frankham

Hi

I have a query which is sorting its results by certain values (as opposed to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top of the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake
 
The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.
 
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

--
Duane Hookom
MS Access MVP


Fredg said:
The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jacob Frankham said:
Hi

I have a query which is sorting its results by certain values (as
Click to expand...
opposed
to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top of the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake
Click to expand...
Click to expand...
 
PERFECT

Thanks Guys !!

Jake

Duane Hookom said:
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

--
Duane Hookom
MS Access MVP


Fredg said:
The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
Click to expand...
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))
In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jacob Frankham said:
Hi

I have a query which is sorting its results by certain values (as
Click to expand...
opposed
to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top
Click to expand...
of
the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake
Click to expand...
Click to expand...
Click to expand...
 
Duane said:
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])
Click to expand...

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortotder field, but very
clever none the less.
 
Marshall Barton said:
Duane said:
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])
Click to expand...

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortotder field, but very
clever none the less.
Click to expand...

Just don't ask him how to do multiplication in a query! <g, d&r>
 
Douglas said:
Marshall Barton said:
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])
Click to expand...

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortorder field, but very
clever none the less.
Click to expand...

Just don't ask him how to do multiplication in a query! <g, d&r>
Click to expand...

Good shot Doug, but we don't have to ask. He announces it
without any prompting. ;-)

For those that weren't there,

Product(field) = Exp(Sum(Log(field)))

Really, it's just some simple math <g, d & r right with
you>
 
You guys are picking on an almost grandpa so try to show a little more
respect.

--
Duane Hookom
MS Access MVP


Marshall Barton said:
Douglas said:
Marshall Barton said:
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

Gotta give you points for cleverness with this one, Duane.

Not as robust as a table with a sortorder field, but very
clever none the less.
Click to expand...

Just don't ask him how to do multiplication in a query! <g, d&r>
Click to expand...

Good shot Doug, but we don't have to ask. He announces it
without any prompting. ;-)

For those that weren't there,

Product(field) = Exp(Sum(Log(field)))

Really, it's just some simple math <g, d & r right with
you>
Click to expand...
 
Duane said:
You guys are picking on an almost grandpa so try to show a little more
respect.
Click to expand...

Oh? OK, that can be a trying experience so we'll cut you a
little slack this time. But, looking back in this thread,
you started it with your clever code so you can't be too
distracted ;-)
 
Yes, v clever code Duane, thanks for that

Thanks also to Freg

Doug, keep up the Math

Duane, congratulations.

Does that cover everything?!?!?!

Jake !
 
Nice one, Duane. How does it handle Nulls?

Perhaps by using "AdvisoryLowMediumHigh",Nz([Risk],"") and Descending sort?



Duane said:
Or
SortOrder:Instr("HighMediumLowAdvisory",[Risk])

--
Duane Hookom
MS Access MVP

Fredg said:
The only sure way to sort a report is in the report's Sorting and Grouping
dialog.

If you want your Report sorted in the order of H,M,L,A then add a new column
to the query.
SortOrder:IIf([Risk]="High",1,IIf([Risk]="Medium",2,IIf([Risk]="Low",3,4)))

In the report, set the Sorting and Grouping dialog to sort ascending by this
SortOrder field.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Jacob Frankham said:
Hi

I have a query which is sorting its results by certain values (as
Click to expand...
opposed
to
alphabetically)

ie

[risk]=

High
Medium
Low
Advisory

ie all of the records whose [risk] field is "High" appear at the top of the
query results followed by all of the "Mediums" etc etc.

However, when I go into 'Sorting and Grouping' on my report and I select
[risk] it only gives me the option of Ascending or Descending - so the work
that my query has done in sorting these into the preferred order is now
lost??

Any help out there?

Cheers

Jake
Click to expand...
Click to expand...
Click to expand...
 
Thanks guys. Ask me some time about my theory of "Cartesian Problem Solving"
techniques.
 
Back
Top